MySQL: delete a row ignoring foreign key constraint

Question

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?

Be aware that you can mess up your DB while using SET foreign_key_checks = 0. Use it only if you know exactly what will be it outcome. I use it only for my php backup script. — DevWL, Feb 15 '14 at 15:16
Of course, doing so will be dangerous and I will use it only when I know exactly what I'm doing. — Xavier_Ex, Feb 16 '14 at 02:37

score 176 · Accepted Answer · edited Apr 18 '19 at 19:47

176

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;

edited Apr 18 '19 at 19:47

Denialos

956
7
16

answered Mar 28 '12 at 17:34

Matt Dodge

10,833
7
38
58

3

i guess you don't have to set foreign key check back to 1 if you're immediately logging out of the session. – dewd Apr 28 '14 at 11:34
i have multiple table so i want to delete this field from all table so how can i do this. this will not working – karan Jul 21 '15 at 15:23
4

Bear in mind that this will leave orphaned rows, it doesn't force delete any child records. – Luke Nov 04 '17 at 10:37
You might want to run an [integrity check](https://stackoverflow.com/a/5977191/3779853) afterwards – phil294 Jul 16 '21 at 04:38
SET foreign_key_checks = 0; doesn't work for me. I still get `Cannot delete or update a parent row: a foreign key constraint fails` errors. – James Parker Jul 16 '21 at 17:34

score 14 · Answer 2 · answered Dec 03 '20 at 12:25

Simply execute as follows:

Disable foreign key check

SET foreign_key_checks = 0;
Delete your records

DELETE FROM table_name WHERE {conditions};
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

score 2 · Answer 3 · answered May 01 '21 at 09:42

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;

score 0 · Answer 4 · answered Dec 28 '20 at 11:23

To expand on the accepted answer, you have to specify the constraint name after DROP FOREIGN KEY

You can check the constraint name by issuing SHOW CREATE TABLE.

> SHOW CREATE TABLE tbl_name

Create Table: CREATE TABLE `tbl_name` (
  `id` int(11) DEFAULT NULL,
  `foo_id` int(11) DEFAULT NULL,
  CONSTRAINT `foo_ibfk_1` FOREIGN KEY (`foo_id`)
)

In this case, "foo_ibfk_1" is the constraint name. So you can write:

ALTER TABLE tableName DROP FOREIGN KEY foo_ibfk_1;

MySQL: delete a row ignoring foreign key constraint

4 Answers4