// Oracle error reference

ORA-02291: integrity constraint violated - no parent key

An INSERT or UPDATE set a foreign key to a value that doesn't exist in the parent table. Insert the parent first or fix the value.

Error code
ORA-02291
Category
Constraints and keys
Error message
integrity constraint (string.string) violated - parent key not found

What it means

A foreign key requires every non-null value in the child column to exist in the referenced parent key. ORA-02291 means the new or changed child row points at a parent that isn't there. The message names the foreign key constraint.

Common causes

  • Inserting child rows before their parent rows, for example loading tables in the wrong order.
  • A typo or wrong id in the foreign key column.
  • The parent row exists but hasn't been committed by another session, so this session can't see it.
  • Data copied from another environment where the parent ids differ.

How to fix it

  1. Find the parent table and columns of the constraint.

    SELECT c.constraint_name, c.table_name, p.table_name AS parent_table, pc.column_name
    FROM all_constraints c
    JOIN all_constraints p ON p.owner = c.r_owner AND p.constraint_name = c.r_constraint_name
    JOIN all_cons_columns pc ON pc.owner = p.owner AND pc.constraint_name = p.constraint_name
    WHERE c.constraint_name = 'ORDERS_CUSTOMER_FK';
  2. Insert the parent row first, then the child.

  3. For loads where order is hard to control, make the constraint deferrable so it is checked at commit.

    ALTER TABLE orders DROP CONSTRAINT orders_customer_fk;
    ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
        FOREIGN KEY (customer_id) REFERENCES customers (id)
        DEFERRABLE INITIALLY DEFERRED;

Related errors

More constraints and keys errors

The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.

// fewer errors, faster fixes

Work on Oracle in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for Oracle and the other databases you use.