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
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
-
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';
-
Insert the parent row first, then the child.
-
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
ORA-02292integrity constraint violated - child record foundORA-00001unique constraint violated23503PostgreSQL: foreign key constraint violation1452MySQL: Foreign key fails: cannot add child row547SQL Server: Statement conflicted with a constraintSQLITE_CONSTRAINT_FOREIGNKEYSQLite: FOREIGN KEY constraint failed
More constraints and keys errors
ORA-01400cannot insert NULL into columnORA-02449unique/primary keys referenced by foreign keysORA-02266keys referenced by enabled foreign keys
The code and message text are from the Oracle Database Error Help. Browse the full Oracle error list or search every database error.
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.