MySQL: delete a row ignoring foreign key constraint

mysql foreign-keys
Xavier_Ex picture Xavier_Ex · Mar 28, 2012 · Viewed 61.1k times · Source

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?

Answer

Matt Dodge picture Matt Dodge · Mar 28, 2012

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;

Related questions

Add Foreign Key to existing table

I want to add a Foreign Key to a table called "katalog". ALTER TABLE katalog ADD CONSTRAINT `fk_katalog_sprache` FOREIGN KEY (`Sprache`) REFERENCES `Sprache` (`ID`) ON DELETE SET NULL ON UPDATE SET NULL; When I try to do this, …

Read More
How to truncate a foreign key constrained table?

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID)) …

Read More
MySQL Error 1215: Cannot add foreign key constraint

I am trying to forward engineer my new schema onto my db server, but I can't figure out why I am getting this error. I've tried to search for the answer here, but everything I've found has said to either …

Read More