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)
This question is related to
mysql
foreign-keys
foreign-key-relationship
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.
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 index
clause)ALTER TABLE DROP FOREIGN KEY fk_name;
ALTER TABLE ADD FOREIGN KEY fk_name(fk_cols)
REFERENCES tbl_name(pk_names) ON DELETE RESTRICT;
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'
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
Source: Stackoverflow.com