Oracle ORA-02292 Error: Causes and Solutions Complete Guide

ORA-02292: integrity constraint violated - child record found

ORA-02292 is a referential integrity error that occurs when you attempt to delete or update a parent record that is still being referenced by one or more child records through a Foreign Key constraint. Oracle throws this error to protect data consistency and prevent orphaned records from appearing in child tables. It is one of the most common constraint violations encountered in day-to-day Oracle database operations.

---

Top 3 Causes

1. Deleting a Parent Record While Child Records Still Exist

The most frequent cause. You try to DELETE a row from a parent table, but at least one row in the child table still holds a Foreign Key value pointing to it.

-- This will raise ORA-02292 if customer 1001 has related orders
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 1001;

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

Fix: Delete child records first, then the parent.

-- Step 1: Remove child records
DELETE FROM ORDERS WHERE CUSTOMER_ID = 1001;

-- Step 2: Now safely remove the parent
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 1001;

COMMIT;

2. Updating a Primary Key Value Referenced by a Foreign Key

Changing the Primary Key value of a parent record when no ON UPDATE CASCADE rule is defined will also trigger ORA-02292, since existing child records would be left pointing to a non-existent parent key.

-- Attempting to change a PK value that child rows reference
UPDATE CUSTOMERS
SET CUSTOMER_ID = 9999
WHERE CUSTOMER_ID = 1001;

-- ORA-02292: integrity constraint violated - child record found

Fix: Either update child FK values first, or recreate the constraint with a cascade rule.

-- Update child FK first
UPDATE ORDERS SET CUSTOMER_ID = 9999 WHERE CUSTOMER_ID = 1001;

-- Then update the parent PK
UPDATE CUSTOMERS SET CUSTOMER_ID = 9999 WHERE CUSTOMER_ID = 1001;

COMMIT;

3. Incorrect Deletion Order in Batch or Migration Jobs

During bulk data cleanup or migration scripts, processing parent tables before child tables is a classic mistake, especially when the full chain of relationships is not mapped out beforehand.

-- Wrong order: deleting parent before children
DELETE FROM CUSTOMERS WHERE REGION = 'ASIA'; -- ORA-02292!

-- Correct order: cascade from deepest child upward
DELETE FROM ORDER_ITEMS
WHERE ORDER_ID IN (
    SELECT ORDER_ID FROM ORDERS O
    JOIN CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
    WHERE C.REGION = 'ASIA'
);

DELETE FROM ORDERS
WHERE CUSTOMER_ID IN (
    SELECT CUSTOMER_ID FROM CUSTOMERS WHERE REGION = 'ASIA'
);

DELETE FROM CUSTOMERS WHERE REGION = 'ASIA';

COMMIT;

---

Quick Fix: Temporarily Disable the Constraint

For bulk operations, you can temporarily disable the Foreign Key constraint. Always re-enable it immediately after the operation.

-- Disable the FK constraint
ALTER TABLE ORDERS DISABLE CONSTRAINT FK_ORDERS_CUSTOMER_ID;

-- Perform your delete
DELETE FROM CUSTOMERS WHERE CUSTOMER_ID = 1001;

-- Re-enable and validate
ALTER TABLE ORDERS ENABLE CONSTRAINT FK_ORDERS_CUSTOMER_ID;

---

Quick Fix: Add ON DELETE CASCADE

If child records should automatically be removed when a parent is deleted, recreate the Foreign Key with ON DELETE CASCADE.

-- Drop old constraint
ALTER TABLE ORDERS DROP CONSTRAINT FK_ORDERS_CUSTOMER_ID;

-- Recreate with cascade delete
ALTER TABLE ORDERS
ADD CONSTRAINT FK_ORDERS_CUSTOMER_ID
FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMERS(CUSTOMER_ID)
ON DELETE CASCADE;

---

Prevention Tips

• Always map FK relationships before bulk operations. Query USER_CONSTRAINTS and USER_CONS_COLUMNS to identify all child tables before running any DELETE or UPDATE on parent tables.

-- Find all tables referencing CUSTOMERS
SELECT C.TABLE_NAME AS CHILD_TABLE, CC.COLUMN_NAME AS FK_COLUMN
FROM USER_CONSTRAINTS C
JOIN USER_CONS_COLUMNS CC ON C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
JOIN USER_CONSTRAINTS P  ON C.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME
WHERE P.TABLE_NAME = 'CUSTOMERS'
  AND C.CONSTRAINT_TYPE = 'R';

• Define your delete strategy at design time. Decide upfront whether to use ON DELETE CASCADE, ON DELETE SET NULL, or the default RESTRICT behavior. Document the decision so all developers follow a consistent approach and avoid runtime surprises in production.

---

Related Errors

• ORA-02291 – Parent key not found (the mirror of ORA-02292; triggered on INSERT/UPDATE of a child record with no matching parent).
• ORA-00001 – Unique constraint violated (common companion error during data load operations).

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.