Find which rows where foreign key constraint fail

foreign-key-relationship, foreign-keys, mysql, sql

Solution

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;

Problem

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

Original source