Find which rows where foreign key constraint fail

Question

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??

Answer 1

Assuming you have following table, and FK relationship.

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;