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 1:

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;

Solution 2:

Simply execute as follows:

1. Disable foreign key check

   SET foreign_key_checks = 0;

2. Delete your records

   DELETE FROM table_name WHERE {conditions};

3. Enable foreign key check

   SET foreign_key_checks = 1;

Credit: https://www.knowledgewalls.com/johnpeter/books/mysql/how-to-ignore-constraints-while-insertupdate-or-delete-records-in-mysql

Solution 3:

As some people already pointed out, ignoring a restricting foreign key leaves you with database inconsistencies. Preventing DELETEs is something you want in such cases.

You should better delete depending rows prior to the main query:

DELETE FROM cities WHERE country_id=3;
-- Afterwards you delete rows from the parent table without error:
DELETE FROM countries WHERE country_id=3;

Or, even better, change the foreign key once, so it does the deletion automatically (cascading):

ALTER TABLE cities DROP FOREIGN KEY `fk.cities.country_id`;
ALTER TABLE cities ADD CONSTRAINT `fk.cities.country_id` FOREIGN KEY (country_id)
    REFERENCES countries (id) ON UPDATE CASCADE ON DELETE CASCADE;
-- From now on, just delete from the parent table:
DELETE FROM countries WHERE country_id=3;