DELETE all where MySQL foreign key constraint does not fail

Question

I am trying to delete a few records but am getting the following error:

Cannot delete or update a parent row: a foreign key constraint fails

The thing is, the foreign key constraint is failing for only 1 or 2 of my 100 records I wish to delete. I wish to write a query which deletes these 98-99 records, skipping the 1 or 2 which failed, which I can later manually inspect and delete/modify. Not stopping because of some single problematic record, but continuing with the others, ignoring that.

Is there a neat way to do this ?

score 8 · Accepted Answer · answered Jan 15 '13 at 17:28

8

You have to LEFT JOIN the referencing table and add a condition saying that the row is missing in that table.

For example:

DELETE a FROM a
LEFT JOIN b ON b.a_id = a.id
WHERE b.a_id IS NULL;

answered Jan 15 '13 at 17:28

crishoj

5,660
4
32
31

score 2 · Answer 2 · answered Aug 08 '11 at 06:08

2

Use ignore:

DELETE IGNORE ...

http://dev.mysql.com/doc/refman/5.0/en/delete.html

answered Aug 08 '11 at 06:08

Pelshoff

1,464
10
13

14

`DELETE IGNORE` doesn't achieve the goal. According to http://www.mysqlperformanceblog.com/2012/02/02/stop-delete-ignore-on-tables-with-foreign-keys-can-break-replication/, in 5.0, _no_ rows are deleted in case of a FOREIGN KEY error, but in 5.1 and 5.5, rows _up until_ the error occurs are deleted, and _subsequent rows are left untouched_. – crishoj Jan 15 '13 at 17:17
1

Neither did I, actually, until I noticed recently that my log purge cron job wasn't really doing its job :) – crishoj Jan 21 '13 at 04:03

DELETE all where MySQL foreign key constraint does not fail

2 Answers2

Linked