ORA-02292: integrity constraint violated - child record found
ORA-02292: integrity constraint (LAB_A.ORDERS_CUST_FK) violated - child record foundYou 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
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
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;Table created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.DELETE FROM customers WHERE cust_id = 1;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/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;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.
DELETE FROM orders WHERE cust_id = 1;
DELETE FROM customers WHERE cust_id = 1;
COMMIT;
SELECT cust_id, name FROM customers;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
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;Table created.
Table created.
1 row created.
1 row created.
1 row created.
Commit complete.UPDATE dept SET dept_code = 'S10' WHERE dept_code = 'D10';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/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;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.
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;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
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;Table created.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Commit complete.DELETE FROM vendor v
WHERE vendor_id > (SELECT MIN(x.vendor_id) FROM vendor x WHERE x.biz_no = v.biz_no);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/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;1 row updated.
Commit complete.Common when cleaning up customer or vendor masters. Move the transactions to the surviving record first, then delete.
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;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