查找在哪些行中外键约束失败。

While trying to add a foreign Key constraint to two very large tables, I get the error.

当尝试向两个非常大的表添加外键约束时，我得到了错误。

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

This is usually due to some data in the primary table not present in the foreign table, normally I check for known anomalies like null values, out of range values etc, once these are taken care of, I can get the constraint satisfied. However this time the problem is more subtle.

这通常是由于主表中的一些数据不在外表中，通常我检查已知的异常，如空值、超出范围值等，一旦处理好这些数据，我就可以得到满足的约束。然而，这一次的问题更加微妙。

What I want to know is there any way to query for all the rows which are causing the constraint to fail??

我想知道的是有没有办法查询导致约束失效的所有行?

2 个解决方案

#1

Assuming you have following table, and FK relationship.

假设你有下表和FK关系。

parent(parent_id (PK), name)
child(child_id, name, parent_id (FK));

You could find which rows are missing in parent table but exists in child table, using following LEFT JOIN:

您可以使用下面的左连接查找父表中缺少但子表中存在的行:

SELECT child.parent_id
FROM child LEFT JOIN parent ON child.parent_id = parent.parent_id
WHERE parent.parent_id IS NULL;

#2

The solution by InoS Heo will work. Here is another way.

InoS Heo的解是有效的。这是另一种方式。

SELECT *
FROM child
WHERE NOT key IN (
    SELECT key
    FROM parent
);

Of course key is the target field(s) you intend to put the constraint on later.

当然，key是目标字段，稍后您将对其进行约束。

#1