删除所有MySQL外键约束不会失败的地方

时间:2022-05-25 04:36:59

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.

问题是,外键约束在我想要删除的100条记录中只有1或2条失败。我希望编写一个删除这些98-99记录的查询,跳过失败的1或2,我稍后可以手动检查和删除/修改。不是因为一些有问题的记录而停止,而是继续其他人,忽略了这一点。

Is there a neat way to do this ?

有一个巧妙的方法来做到这一点?

2 个解决方案

#1


6  

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

您必须LEFT JOIN引用表并添加一个条件,表示该表中缺少该行。

For example:

例如:

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

#2


1  

Use ignore:

使用ignore:

DELETE IGNORE ...

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

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

#1


6  

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

您必须LEFT JOIN引用表并添加一个条件,表示该表中缺少该行。

For example:

例如:

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

#2


1  

Use ignore:

使用ignore:

DELETE IGNORE ...

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

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