ORA-02291 means a row was inserted or updated with a foreign key value that does not exist in the parent table. The foreign key constraint protects the relationship: an order line must belong to an existing order. Either the parent row is missing, it has not been inserted yet in the right order, or the value itself is wrong.
Reproduce the Error
Example:
create table demo_orders2 (order_id number constraint demo_orders2_pk primary key); create table demo_order_lines ( line_id number primary key, order_id number constraint demo_lines_order_fk references demo_orders2 ); insert into demo_orders2 values (100); commit; insert into demo_order_lines values (1, 100); commit; insert into demo_order_lines values (2, 999);
Output:
Table DEMO_ORDERS2 created. Table DEMO_ORDER_LINES created. 1 row inserted. Commit complete. 1 row inserted. Commit complete. Error starting at line : 10 In command - insert into demo_order_lines values (2, 999) Error report - ORA-02291: integrity constraint (NIMBUS.DEMO_LINES_ORDER_FK) violated - parent key not found
Order 100 exists, so line 1 is accepted. Order 999 does not, so line 2 is rejected. The message names the constraint, DEMO_LINES_ORDER_FK.
Fix: Parent First, and Check Before Loading
Example:
-- insert the parent first, then the child, in one transaction insert into demo_orders2 values (999); insert into demo_order_lines values (2, 999); select l.line_id, l.order_id from demo_order_lines l order by l.line_id; -- before loading, find staged lines whose order does not exist with staged (line_id, order_id) as (values (3, 777), (4, 100)) select s.line_id, s.order_id from staged s where not exists (select 1 from demo_orders2 o where o.order_id = s.order_id);
Output:
1 row inserted.
1 row inserted.
LINE_ID ORDER_ID
__________ ___________
1 100
2 999
LINE_ID ORDER_ID
__________ ___________
3 777- Inserting order 999 before its line satisfies the constraint.
- Before loading data, NOT EXISTS lists the staged lines whose order is missing, here line 3 for order 777.
Things to Know
- When parent and child arrive in one load in any order, make the constraint DEFERRABLE INITIALLY DEFERRED so it is checked at commit.
- USER_CONSTRAINTS shows which parent table and key a constraint refers to, through R_CONSTRAINT_NAME.
- Do not disable the constraint to make a load pass; orphan rows are hard to clean up later.
Related Guides
Conclusion
ORA-02291 means a child row points to a parent key that does not exist. Insert parents first, find orphans with NOT EXISTS before loading, and defer the constraint when both arrive together.
