Alter a foreign key in mySQL

I recently came across a situation where the foreign key was incorrectly defined in a table I was working with and I had to figure out how to redefine it…

Lets say we have a table Owners and Pets. Now Pets are linked to Owners via a foreign key owners_id or something similar.

The foreign key definition looked like this:

FOREIGN KEY (`owner_id`) REFERENCES `owners`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION;

When I try to delete an owner however, I also want the corresponding pet to be deleted since I don’t have any use for a pet without an owner. The foreign key definition didn’t allow me to do this because it violated the foreign key constraint. The solution is to redefine the foreign key to cascade on delete.

So the SQL to achieve this in mySQL is as follows: First drop your foreign key. For this you will need the foreign key name. If you’re not sure what it is, you can look it up like so:

SHOW CREATE TABLE `pets`

This will output your table structure together with the foreign key names and definitions. Now to change your foreign key, you first have to drop it (using the name) and then create a new foreign key with the correct definition and your done!

ALTER TABLE `pets` DROP FOREIGN KEY `your_fk_name_here`;
ALTER TABLE `pets` ADD FOREIGN KEY (`owner_id`) REFERENCES `owners`(`id`) ON UPDATE CASCADE ON DELETE CASCADE;
or
ALTER TABLE `pets` ADD CONSTRAINT fk_owner_pet FOREIGN KEY (`owner_id`) REFERENCES `owners`(`id`) ON UPDATE CASCADE ON DELETE CASCADE;

The second variation is to give your constraint a name, so you can easily drop it next time without first having to find the name.

Hope you found this tip useful. :)

Leave a Reply