Add on delete cascade to foreign key mysql

Here’s a quick process for adding ON DELETE CASCADE to your foreign key constraints in mySQL. You will need to drop the foreign key constraint and re-institute it.

  1. use SHOW CREATE TABLE [table_name];, where table_name is your table
  2. identify the name of the foreign key(s). They usually end with _ibfk_ plus a number
  3. Drop the foreign key constraint using ALTER TABLE [table_name] DROP FOREIGN KEY [foreign key];
  4. Re-create the foreign key constraint ALTER TABLE [table_name] ADD CONSTRAINT [foreign key name from above] FOREIGN KEY (`[column_name]`) REFERENCES `[other_table]` (`[column_name]`)
    ON DELETE CASCADE;

And for those in a real hurry:

SHOW CREATE TABLE [table_name]

ALTER TABLE [table_name] DROP FOREIGN KEY [foreign key];

ALTER TABLE [table_name]
ADD CONSTRAINT [foreign key name from above]
FOREIGN KEY (`[column_name]`) REFERENCES `[other_table]` (`[column_name]`)
ON DELETE CASCADE;

Leave a Reply