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

user_81785 0
Viewed 159999

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)

foreign-key-relationship foreign-keys mysql
6 Answers

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_61679160

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_60769840
Related