Find which rows where foreign key constraint fail

mysql sql foreign-keys foreign-key-relationship

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_idFROM child LEFT JOIN parent ON child.parent_id = parent.parent_idWHERE parent.parent_id IS NULL;

mysql sql foreign-keys foreign-key-relationship

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

SELECT *FROM childWHERE NOT key IN (    SELECT key    FROM parent);

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

CodeHunter