ORA-02292: integrity constraint violated - child record found

ORA-02292: integrity constraint (SCHEMA.CONSTRAINT) violated - child record found

You tried to delete or change a parent row that child rows still reference. Delete or reassign the child rows first, or use ON DELETE CASCADE if that is the real rule.

What ORA-02292 means

A foreign key prevents orphan rows, so a parent row cannot be deleted while children point to it. The message names the constraint; the child table is found through the data dictionary. Dropping a whole parent table has its own error, ORA-02449.

Common causes of ORA-02292

Deleting a parent row that has child rows.
Updating a parent key that children reference.
A child table you did not know about, for example in another schema.
Clean-up scripts that delete tables in the wrong order.

How to fix ORA-02292

Find the child table through the constraint name in ALL_CONSTRAINTS.
Decide what should happen to the children: delete them first, or move them to another parent.
If children should always go with their parent, define the foreign key with ON DELETE CASCADE. Think twice: a single delete then removes much more.
In clean-up scripts, delete children before parents.

ORA-02292 example

Which table holds the children?

SELECT owner, table_name
  FROM all_constraints
 WHERE constraint_name = 'FK_ORDERS_CUSTOMER';

Children first, then the parent:

DELETE FROM orders    WHERE customer_id = 42;
DELETE FROM customers WHERE id = 42;

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-02292. Oracle is a registered trademark of Oracle Corporation. SQLlightspeed is not affiliated with Oracle.