ENUM column auto set

Question

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')

Answer 1

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.