🧩 How to Fix ORA-02292: Integrity Constraint Violated – Child Record Found

🧩 Introduction:

The ORA-02292 error occurs when you try to delete a record from a parent table while related rows still exist in the child table. This happens due to a foreign key constraint that enforces referential integrity between tables. As a DBA or developer, you must handle this carefully to avoid breaking relationships or losing data.

---

Step 1: Understand the Error

When you execute a DELETE on a parent row, Oracle checks if related child rows exist. If they do, Oracle prevents deletion and raises ORA-02292.

Example:

DELETE FROM departments WHERE department_id = 10;

➡️ If employees still exist in department 10, this error occurs.

---

Step 2: Identify the Constraint and Child Table

To find the foreign key causing the error, query the data dictionary:

SELECT constraint_name, table_name

FROM user_constraints

WHERE r_constraint_name = (

  SELECT constraint_name

  FROM user_constraints

  WHERE table_name = 'DEPARTMENTS'

  AND constraint_type = 'P'

);

➡️ This will show the child table(s) and constraints referencing the parent.

---

Step 3: Solutions to Resolve ORA-02292

1. Delete Child Records First

Ensure no dependent rows exist before deleting the parent row:

DELETE FROM employees WHERE department_id = 10;

DELETE FROM departments WHERE department_id = 10;

2. Use ON DELETE CASCADE Constraint

Modify the foreign key to automatically delete child rows when the parent is deleted:

ALTER TABLE employees

ADD CONSTRAINT fk_dept

FOREIGN KEY (department_id)

REFERENCES departments(department_id)

ON DELETE CASCADE;

⚠️ Be cautious — this will remove all related child rows automatically.

3. Use ON DELETE SET NULL

Instead of deleting child rows, set their foreign key to NULL:

ALTER TABLE employees

ADD CONSTRAINT fk_dept

FOREIGN KEY (department_id)

REFERENCES departments(department_id)

ON DELETE SET NULL;

---

Step 4: Example Scenario

If a department has employees assigned to it, deleting the department directly will trigger ORA-02292. By either removing employees first, or defining ON DELETE CASCADE, you can manage deletion safely.

---

Step 5: Best Practices

Avoid deleting parent records unless necessary.

Use cascading deletes carefully to prevent accidental mass deletions.

Always back up data before performing bulk deletes.

Regularly audit foreign key constraints to ensure data integrity.

---

Conclusion:

ORA-02292 protects your database from orphaned records by enforcing referential integrity. By identifying the child records and applying the correct strategy — deleting dependents, using cascading rules, or setting NULL — you can resolve the error while maintaining database consistency.