Search code examples
mysqlforeign-keys

MySQL: delete a row ignoring foreign key constraint

so I am working on a few tables and there are some data inconsistency between them... One or two tables have a foreign key constraint on a particular table (call it table X), but that table has multiple rows with the foreign key column.

What I want to do is to remove the duplicated rows in table X, but the foreign key constraint is preventing me from doing this. Is there a way to force delete the rows while ignoring the foreign key constraint since I know what I'm doing?

Solution

  • SET foreign_key_checks = 0;

    That will prevent MySQL from checking foreign keys. Make sure to set it back to 1 when you are done though.

    Also, you could always drop the foreign key and then add it later if you wanted to only affect a singular key

    ALTER TABLE tableName DROP FOREIGN KEY fk;