ORA-02292: integrity constraint violated - child record found

ORA-02292: integrity constraint (LAB_A.ORDERS_CUST_FK) violated - child record found

You tried to delete or change a parent row that child rows still reference.

Lab-verifiedReproduced and fixed on Oracle AI Database 26ai Free 23.26.3.0.0 · 2026-11-14

ORA-02292 is the reverse of ORA-02291. A DELETE or UPDATE on the parent table would leave child rows pointing at a key that no longer exists, so Oracle stops it.

The constraint in the message (e.g. LAB_A.ORDERS_CUST_FK) belongs to the child table. Look it up in user_constraints to see which table and column still reference the row.

Causes and fixes

  1. Deleting a parent row that still has child rows
  2. Changing a primary key value that child rows use
  3. Deleting a duplicate master record that transactions still reference

1Deleting a parent row that still has child rows

Customer 1 has two orders. DELETE FROM customers WHERE cust_id = 1 failed with ORDERS_CUST_FK. Querying user_constraints (constraint_type = 'R', r_constraint_name = 'CUSTOMERS_PK') showed the child is ORDERS.CUST_ID, and orders 1001 and 1002 belong to customer 1.

Deleting the orders first (2 rows deleted) and then the customer (1 row deleted) worked.

Setup
Setuprun as lab_a
CREATE TABLE customers (cust_id NUMBER CONSTRAINT customers_pk PRIMARY KEY, name VARCHAR2(30));
CREATE TABLE orders (order_id NUMBER PRIMARY KEY, cust_id NUMBER CONSTRAINT orders_cust_fk REFERENCES customers, amount NUMBER);
INSERT INTO customers VALUES (1, 'Kim');
INSERT INTO customers VALUES (2, 'Lee');
INSERT INTO orders VALUES (1001, 1, 500);
INSERT INTO orders VALUES (1002, 1, 300);
INSERT INTO orders VALUES (1003, 2, 700);
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
DELETE FROM customers WHERE cust_id = 1;
OUTPUT
DELETE FROM customers WHERE cust_id = 1
*
ERROR at line 1:
ORA-02292: integrity constraint (LAB_A.ORDERS_CUST_FK) violated - child record found
Help: https://docs.oracle.com/error-help/db/ora-02292/
Checkrun as lab_a
COLUMN constraint_name FORMAT A16
COLUMN table_name FORMAT A10
COLUMN column_name FORMAT A10
SELECT c.constraint_name, c.table_name, cc.column_name
  FROM user_constraints c JOIN user_cons_columns cc ON cc.constraint_name = c.constraint_name
 WHERE c.constraint_type = 'R'
   AND c.r_constraint_name = 'CUSTOMERS_PK';
SELECT order_id, amount FROM orders WHERE cust_id = 1 ORDER BY order_id;
OUTPUT
CONSTRAINT_NAME  TABLE_NAME COLUMN_NAM
---------------- ---------- ----------
ORDERS_CUST_FK   ORDERS     CUST_ID

1 row selected.


  ORDER_ID     AMOUNT
---------- ----------
      1001        500
      1002        300

2 rows selected.

Make sure the child rows really can go. If they are history you need to keep, don't delete the parent; mark it inactive instead.

After the fixrun as lab_a
DELETE FROM orders WHERE cust_id = 1;
DELETE FROM customers WHERE cust_id = 1;
COMMIT;
SELECT cust_id, name FROM customers;
OUTPUT
2 rows deleted.


1 row deleted.


Commit complete.


   CUST_ID NAME
---------- ------------------------------
         2 Lee

1 row selected.

2Changing a primary key value that child rows use

Renaming department code D10 to S10 with UPDATE dept SET dept_code = ... failed with EMP_DEPT_FK, because two employees still use D10.

The change was done in three steps: insert a new S10 row copied from D10, move the employees to S10 (2 rows updated), then delete D10. The result: S10 Sales with 2 employees.

Setup
Setuprun as lab_a
CREATE TABLE dept (dept_code VARCHAR2(10) CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(20));
CREATE TABLE emp (emp_id NUMBER PRIMARY KEY, name VARCHAR2(20), dept_code VARCHAR2(10) CONSTRAINT emp_dept_fk REFERENCES dept);
INSERT INTO dept VALUES ('D10', 'Sales');
INSERT INTO emp VALUES (101, 'Kim', 'D10');
INSERT INTO emp VALUES (102, 'Lee', 'D10');
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
UPDATE dept SET dept_code = 'S10' WHERE dept_code = 'D10';
OUTPUT
UPDATE dept SET dept_code = 'S10' WHERE dept_code = 'D10'
*
ERROR at line 1:
ORA-02292: integrity constraint (LAB_A.EMP_DEPT_FK) violated - child record found
Help: https://docs.oracle.com/error-help/db/ora-02292/
Fixrun as lab_a
INSERT INTO dept (dept_code, dname) SELECT 'S10', dname FROM dept WHERE dept_code = 'D10';
UPDATE emp SET dept_code = 'S10' WHERE dept_code = 'D10';
COMMIT;
OUTPUT
1 row created.


2 rows updated.


Commit complete.

If there are several child tables, each one has to be moved before the old key is deleted.

After the fixrun as lab_a
DELETE FROM dept WHERE dept_code = 'D10';
COMMIT;
SELECT d.dept_code, d.dname, COUNT(e.emp_id) AS emp_cnt
  FROM dept d LEFT JOIN emp e ON e.dept_code = d.dept_code
 GROUP BY d.dept_code, d.dname;
OUTPUT
1 row deleted.


Commit complete.


DEPT_CODE  DNAME                   EMP_CNT
---------- -------------------- ----------
S10        Sales                         2

1 row selected.

3Deleting a duplicate master record that transactions still reference

Vendors 1 and 2 are the same company (same biz_no). Deleting the newer duplicate failed with PO_VENDOR_FK, because purchase order 502 points at vendor 2.

First the purchase orders were moved to the surviving vendor (the lowest vendor_id with the same biz_no; 1 row updated). The same DELETE then removed vendor 2, and both POs now show vendor 1.

Setup
Setuprun as lab_a
CREATE TABLE vendor (vendor_id NUMBER CONSTRAINT vendor_pk PRIMARY KEY, vendor_name VARCHAR2(30), biz_no VARCHAR2(12));
CREATE TABLE po (po_id NUMBER PRIMARY KEY, vendor_id NUMBER CONSTRAINT po_vendor_fk REFERENCES vendor, amount NUMBER);
INSERT INTO vendor VALUES (1, 'Hanil Steel', '123-45-67890');
INSERT INTO vendor VALUES (2, 'Hanil Steel Co.', '123-45-67890');
INSERT INTO po VALUES (501, 1, 1000);
INSERT INTO po VALUES (502, 2, 2500);
COMMIT;
OUTPUT
Table created.


Table created.


1 row created.


1 row created.


1 row created.


1 row created.


Commit complete.
Reproducerun as lab_a
DELETE FROM vendor v
 WHERE vendor_id > (SELECT MIN(x.vendor_id) FROM vendor x WHERE x.biz_no = v.biz_no);
OUTPUT
DELETE FROM vendor v
*
ERROR at line 1:
ORA-02292: integrity constraint (LAB_A.PO_VENDOR_FK) violated - child record found
Help: https://docs.oracle.com/error-help/db/ora-02292/
Fixrun as lab_a
UPDATE po p
   SET vendor_id = (SELECT MIN(x.vendor_id) FROM vendor x
                     WHERE x.biz_no = (SELECT v.biz_no FROM vendor v WHERE v.vendor_id = p.vendor_id))
 WHERE vendor_id IN (SELECT v.vendor_id FROM vendor v
                      WHERE v.vendor_id > (SELECT MIN(x.vendor_id) FROM vendor x WHERE x.biz_no = v.biz_no));
COMMIT;
OUTPUT
1 row updated.


Commit complete.

Common when cleaning up customer or vendor masters. Move the transactions to the surviving record first, then delete.

After the fixrun as lab_a
DELETE FROM vendor v
 WHERE vendor_id > (SELECT MIN(x.vendor_id) FROM vendor x WHERE x.biz_no = v.biz_no);
COMMIT;
SELECT po_id, vendor_id, amount FROM po ORDER BY po_id;
OUTPUT
1 row deleted.


Commit complete.


     PO_ID  VENDOR_ID     AMOUNT
---------- ---------- ----------
       501          1       1000
       502          1       2500

2 rows selected.

FAQ

How do I find the child table that causes ORA-02292?

Query user_constraints with constraint_type = 'R' and r_constraint_name = the parent's primary key name, joined to user_cons_columns for the column. The constraint name in the message is the child's.

How do I delete a parent row with child records in Oracle?

Delete the child rows first, then the parent. In this lab: DELETE FROM orders WHERE cust_id = 1, then DELETE FROM customers WHERE cust_id = 1.

How do I update a primary key that is referenced by a foreign key?

Insert a new parent row with the new key, move the child rows to it, then delete the old parent.

Is ORA-02292 the same as ORA-02291?

They are opposites. ORA-02291 is a child pointing at a missing parent (INSERT/UPDATE on the child). ORA-02292 is a parent being removed while children still point at it.

How we verify

Each scenario starts from fresh database users. A script runs the setup, confirms the exact error code, applies the fix and runs the statement again. The output shown is what the database printed, saved by the script.

lab_a and lab_b are ordinary users created for the test; system is the DBA account. Oracle AI Database 26ai Free Release 23.26.3.0.0 - Develop, Learn, and Run for Free

Related errors