CODEWITHSUNDEEP
mysqlsqlforeign-keysforeign-key-relationship
Manquer
Manquer

Reputation: 7647

Find which rows where foreign key constraint fail

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

Upvotes: 24

Views: 16121

Answers (2)

Lumpy Wizard
Lumpy Wizard

Reputation: 153

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

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.

Upvotes: 13

Jason Heo
Jason Heo

Reputation: 10246

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;

Upvotes: 38

Related Questions