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)

George

Answer 1

_{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
:

Reclaim

Shouldn't the constraint you add be ON DELETE RESTRICT as requested by the original question? – Chiles

Ehm, what is "on delete cascade" and why is that neccessary? – Leesen

@Chiles RESTRICT is the default so you get that when not specifying. – Officeholder

@Leesen "on delete cascade" means that if you delete a row from the parent table (Users in this case) all referencing rows from the child table (UserDetails) are also deleted. – Officeholder

thanks for the notes "notice constraint is deleted, column is not deleted", "adding constraint, column is already there", guess that means data is practically preserved and only the schema changes there – Turnstone

Answer 2

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.

Perutz

the new name could indicate this rather significant property of the new key - fk_name_CASCADE – Astronavigation

Sadly, in MySQL/MariaDB this doesn't work if the new foreign key name is the same as the old ("Duplicate key on write or update"). – Duyne

Answer 3

ALTER TABLE DROP FOREIGN KEY fk_name;
ALTER TABLE ADD FOREIGN KEY fk_name(fk_cols)
            REFERENCES tbl_name(pk_names) ON DELETE RESTRICT;

Rescission

helped me find the solution ALTER TABLE table_name ADD ... ON DELETE RESTRICT – George

No, fk_name is the constraint name. It's optional to provide one. I'm not sure but maybe you can retrieve it using SHOW CREATE TABLE. – Rescission

ON CASCADE RESTRICT is probably not intended. – Loos

Answer 4

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

Norwood

nice point about dropping the index. If I don't drop the index would the db create a duplicate index for the foreign key or would it not create anything and just keep the simple index? – Herrah

Answer 5

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'

Hexamerous

this will not work if a constraint is on multiple columns. the generated sql will create separate constraints for each column – Alcaraz

All of my FKs were on single columns, so I wasn't thinking as much about that possibility, but good thought – Hexamerous

Answer 6

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

Benedetto

this will work only if you change the constraint name (if using an auto-generated name probably it will work, guess MySQL always creates unique ones) – Turnstone

The query works for sure on MySQL / MariaDB. The key here is to drop old constraint by it's name, which is being done at line 2. – Benedetto

all-in-one query syntax didn't work for me with MySQL when explicit constraint names where used – Turnstone