0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.