How to change the foreign key referential action? (behavior)

Moak picture Moak · Jul 29, 2010 · Viewed 135.7k times · Source

I have set up a table that contains a column with a foreign key, set to ON DELETE CASCADE (delete child when parent is deleted)

What would the SQL command be to change this to ON DELETE RESTRICT? (can't delete parent if it has children)

Answer

Grijesh Chauhan picture Grijesh Chauhan · Jan 17, 2013

Old question but adding answer so that one can get help

Its two step process:

Suppose, a table1 has a foreign key with column name fk_table2_id, with constraint name fk_name and table2 is referred table with key t2 (something like below in my diagram).

   table1 [ fk_table2_id ] --> table2 [t2]

First step, DROP old CONSTRAINT: (reference)

ALTER TABLE `table1` 
DROP FOREIGN KEY `fk_name`;  

notice constraint is deleted, column is not deleted

Second step, ADD new CONSTRAINT:

ALTER TABLE `table1`  
ADD CONSTRAINT `fk_name` 
    FOREIGN KEY (`fk_table2_id`) REFERENCES `table2` (`t2`) ON DELETE CASCADE;  

adding constraint, column is already there

Example:

I have a UserDetails table refers to Users table:

mysql> SHOW CREATE TABLE UserDetails;
:
:
 `User_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Detail_id`),
  KEY `FK_User_id` (`User_id`),
  CONSTRAINT `FK_User_id` FOREIGN KEY (`User_id`) REFERENCES `Users` (`User_id`)
:
:

First step:

mysql> ALTER TABLE `UserDetails` DROP FOREIGN KEY `FK_User_id`;
Query OK, 1 row affected (0.07 sec)  

Second step:

mysql> ALTER TABLE `UserDetails` ADD CONSTRAINT `FK_User_id` 
    -> FOREIGN KEY (`User_id`) REFERENCES `Users` (`User_id`) ON DELETE CASCADE;
Query OK, 1 row affected (0.02 sec)  

result:

mysql> SHOW CREATE TABLE UserDetails;
:
:
`User_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`Detail_id`),
  KEY `FK_User_id` (`User_id`),
  CONSTRAINT `FK_User_id` FOREIGN KEY (`User_id`) REFERENCES 
                                       `Users` (`User_id`) ON DELETE CASCADE
: