Mysql – ENUM column auto set

enumMySQL

I have two tables "using" foreign keys:

  1. Referencing table

    CREATE TABLE `requests` (
`request_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL,
--       
`status` enum('new','approved') DEFAULT NULL,
--       ^^^^
PRIMARY KEY (`request_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

  2. Referenced table

    CREATE TABLE `status` (
`status_id` int(11) NOT NULL AUTO_INCREMENT,
`status_name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`status_id`),
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Now, I want something like this, If I create a new row, say, 'pending' name in the status table then status (list of ENUM values) of requests table automatically updated with 'pending' ENUM value. So, the "final" status allowed ENUM values in the request table would be:

  • status('new','approved','Pending')

Best Answer

Change the status table to match

  CREATE TABLE `status` (
  `status` enum('new','approved') DEFAULT NULL,
  `status_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`status`),
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1

There is no need for AUTO_INCREMENT.

Then do this for each table:

ALTER TABLE ...
  MODIFY COLUMN `status` enum('new','approved','Pending') DEFAULT NULL;

Be sure to change all references to status_id.

Related Question