How to find records that violates foreign key constraints

Question

I'm trying to find records that is violating the ORA-02291: integrity constraint::

I was running this query, But I didnt get any results back::

    SELECT child.parent_id
    FROM child LEFT JOIN parent ON child.parent_id = parent.parent_id
    WHERE parent.parent_id IS NULL;

Am I missing something or what are the other ways to find the records which are violating these constraints.

Answer 1

You can use not exists:

select parent_id
from child c
where not exists (
    select 1
    from parent p
    where p.id = c.parent_id)

• SQL Fiddle Demo

Answer 2

Here is the query to delete orphan records in child table to be able to make the foreign key constraints :

DELETE FROM `child`
WHERE `parent_id` NOT IN (
    SELECT DISTINCT `id`
    FROM `parent`
);