ORA-02291: integrity constraint violated - parent key not found

ORA-02291: integrity constraint (SCHEMA.CONSTRAINT) violated - parent key not found

You inserted or updated a child row whose foreign key value has no matching row in the parent table. Insert the parent first, or correct the value.

What ORA-02291 means

A foreign key guarantees that every child row points to an existing parent row. ORA-02291 means the value you supplied has no parent. The message names the constraint; its columns and parent table are in the data dictionary. Deleting a child-side row never raises it; see ORA-02292 for the parent side.

Common causes of ORA-02291

A wrong or mistyped key value.
Loading child data before parent data.
The parent row was deleted, or it is uncommitted in another session.
Different data between environments: an ID valid in test does not exist in production.

How to fix ORA-02291

Find the parent table and columns of the constraint with ALL_CONSTRAINTS.
Check whether the value exists in the parent table.
Load parents before children, or insert both in one transaction with a DEFERRABLE constraint.
Do not disable the constraint to get past the error: the orphan rows will cause trouble later.

ORA-02291 example

Which table is the parent?

SELECT c.table_name AS child, p.table_name AS parent, p.constraint_name
  FROM all_constraints c
  JOIN all_constraints p
    ON p.owner = c.r_owner AND p.constraint_name = c.r_constraint_name
 WHERE c.constraint_name = 'FK_ORDERS_CUSTOMER';

In SQLlightspeed

When a statement fails, SQLlightspeed’s error panel shows the error code with a plain-language explanation and a suggestion for what to do next, together with Oracle’s own message and a link to Oracle’s documentation — for this error and dozens of other common ones.

Try SQLlightspeed free for 30 days

Oracle documentation for ORA-02291. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.