How to change the foreign key referential action behavior

Question

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

User · Accepted Answer

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   -- gt  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 gt  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 gt  ALTER TABLE  UserDetails  DROP FOREIGN KEY  FK User id   Query OK  1 row affected  0 07 sec      Second step     mysql gt  ALTER TABLE  UserDetails  ADD CONSTRAINT  FK User id       - gt  FOREIGN KEY   User id   REFERENCES  Users    User id   ON DELETE CASCADE  Query OK  1 row affected  0 02 sec      result     mysql gt  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

User · Answer

You can simply use one query to rule them all   ALTER TABLE products DROP FOREIGN KEY oldConstraintName  ADD FOREIGN KEY  product id  category id  REFERENCES externalTableName  foreign key name  another one makes composite key  ON DELETE CASCADE ON UPDATE CASCADE

User · Answer

You can do this in one query if you re willing to change its name   ALTER TABLE table name   DROP FOREIGN KEY  fk name     ADD CONSTRAINT  fk name2  FOREIGN KEY   remote id       REFERENCES  other table    id       ON DELETE CASCADE    This is useful to minimize downtime if you have a large table

User · Answer

ALTER TABLE DROP FOREIGN KEY fk name  ALTER TABLE ADD FOREIGN KEY fk name fk cols              REFERENCES tbl name pk names  ON DELETE RESTRICT

User · Answer

I had a bunch of FKs to alter  so I wrote something to make the statements for me  Figured I d share   SELECT  CONCAT  ALTER TABLE     rc TABLE NAME         DROP FOREIGN KEY     rc CONSTRAINT NAME         CONCAT  ALTER TABLE     rc TABLE NAME         ADD CONSTRAINT     rc CONSTRAINT NAME     FOREIGN KEY     kcu COLUMN NAME          REFERENCES    kcu REFERENCED TABLE NAME        kcu REFERENCED COLUMN NAME     ON DELETE CASCADE     FROM INFORMATION SCHEMA REFERENTIAL CONSTRAINTS rc LEFT OUTER JOIN INFORMATION SCHEMA KEY COLUMN USAGE kcu     ON kcu TABLE SCHEMA   rc CONSTRAINT SCHEMA     AND kcu CONSTRAINT NAME   rc CONSTRAINT NAME WHERE DELETE RULE    NO ACTION  AND rc CONSTRAINT SCHEMA    foo

User · Answer

Remember that MySQL keeps a simple index on a column after deleting foreign key  So  if you need to change  references  column you should do it in 3 steps   drop original FK drop an index  names as previous fk  using drop index clause  create new FK

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

Examples related to mysql

Examples related to foreign-keys

Examples related to foreign-key-relationship