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

Insubordinate

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;

Nutgall

thanks..it works, i was trying something similar before i posted, but guess missed something.. – Insubordinate

@Insubordinate Thanks, you could accomplish using 3 different ways. LEFT JOIN (mine), NOT IN (Lumpy's), NOT EXISTS. but, I guess LEFT JOIN is faster than others. – Nutgall

I was just mentioning the same regarding speed to lumpy. I guess JOIN is the best way to achieve this. – Insubordinate

@Insubordinate I agree with you ;-) – Nutgall

FWIW, this worked well on Oracle Database 12c (Rel. 12.2.0.1.0). – Nubble

Answer 2

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.

Munmro

thanks.. your solution is simpler, but for my large table set it seemed slower – Insubordinate