How do I fix ORA-02291?

Create the foreign keys as DEFERRABLE and defer them for the loading session — checks then happen once, at commit. Or add the foreign keys after the data is in, which also avoids paying for a check on every row. If it fails on the way back in, the source has orphans. Find them before deciding what they mean.

Oracle · error reference

ORA-02291: parent key not found during a data load

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

A child row arrived before its parent. Why load order alone will not save you, and how deferred constraints turn an unsolvable ordering problem into a solved one.

What the database is telling you

The row you inserted carries a foreign key value with no matching row in the referenced table. Oracle checks this per statement by default, so the insert fails the moment it runs rather than at commit.

Why it shows up in a migration

Loading in dependency order handles the simple cases and nothing else. It cannot handle a cycle — orders references customers, customers.primary_order_id references orders — because no order exists that satisfies both. It cannot handle a self-referencing tree loaded in key order, where a manager row may follow its own report. And it cannot handle parallel table loads, which is where the throughput comes from. Any real migration therefore needs the constraints out of the way during the load rather than a cleverer sort.

Common causes

A foreign key cycle between two or more tables.
A self-referencing hierarchy loaded in primary-key order.
Tables loaded in parallel, so a child batch commits before its parent's.
Genuinely orphaned rows in the source, where the source never enforced the constraint.

How to fix it

Create the foreign keys as DEFERRABLE and defer them for the loading session — checks then happen once, at commit.

ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (cust_id)
  REFERENCES customers(id) DEFERRABLE INITIALLY IMMEDIATE;

SET CONSTRAINTS ALL DEFERRED;

2
Or add the foreign keys after the data is in, which also avoids paying for a check on every row.
```
ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (cust_id) REFERENCES customers(id);
```

If it fails on the way back in, the source has orphans. Find them before deciding what they mean.

SELECT o.id, o.cust_id FROM orders o
  LEFT JOIN customers c ON c.id = o.cust_id
 WHERE o.cust_id IS NOT NULL AND c.id IS NULL;

What DBShifts does about it

Foreign-key statements are separated from the rest of the DDL and applied after the data lands — on by default. If a constraint then fails to apply, the migration does not report a clean success: the failure is collected and surfaced, because an unenforced foreign key sitting next to the word 'succeeded' is how silent corruption starts.

Bulk transfer engine

Migrating between engines?

DBShifts converts the schema, moves the data, and then proves the two sides match with a type-aware checksum per table — not just a row count. Errors like this one are handled on the way, and the ones that cannot be handled are reported rather than logged and forgotten.

Start free See the migration paths

Related errors

Oracle

ORA-02292: child record found when deleting

The mirror image of ORA-02291 — you deleted a parent that still has children. Common in CDC replication, where a delete can arrive before the child deletes that preceded it.

SQL Server

SQL Server error 547: FOREIGN KEY constraint conflict

SQL Server's foreign-key violation, and the NOCHECK trap that makes it look solved when it is not — a constraint re-enabled without WITH CHECK is left untrusted.

← All database errors