May 27th, 2009

How to add ON DELETE CASCADE to an existing MySQL table without dropping any existing data

Tom Boutell
Chief Software Architect
You created a MySQL table with a foreign key reference... for instance, a user_id field that refers to a separate table of users.

Then you realized you should have specified "ON DELETE CASCADE" when creating the table. Now, when you try to delete a user, you can't because objects in the other table are still referencing that user object. Ouch.

What to do, what to do!

Do this (substitute your table and column names, of course): SHOW CREATE TABLE pk_context_cms_access; Note the name of the foreign key constraint, which is usually automatically generated, and looks like this: CONSTRAINT `pk_context_cms_access_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`)... Now drop the foreign key constraint. Relax! You're not dropping the column involved, so you're not losing any data. You're just giving up the integrity check that makes sure foreign IDs refer to stuff that really exists and prevents you from deleting records unless you have set ON DELETE CASCADE... which is probably why you are reading this. (This will temporarily relax referential integrity requirements. In English, that means you should probably temporarily disable the site while blasting through these steps, if it serves billyuns of people every micro-moment.) alter table pk_context_cms_access drop foreign key pk_context_cms_access_ibfk_1; Now add the foreign key constraint back again, this time with ON DELETE CASCADE: alter table pk_context_cms_access add foreign key(user_id) references sf_guard_user(id) on delete cascade; Boom. Check "show create table pk_context_cms_access" again to see that it worked: CONSTRAINT `pk_context_cms_access_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`) ON DELETE CASCADE... Now buy me a beer.
Tom Boutell
Chief Software Architect