ORA-02292 means a DELETE, or an UPDATE of a key, would leave child rows pointing at a parent that no longer exists. The foreign key refuses it. The fix depends on what should happen to the children: delete them first, let the database delete them automatically, or keep them and leave the parent alone.
Reproduce the Error
Example:
create table demo_projects (project_id number constraint demo_projects_pk primary key, name varchar2(30)); create table demo_tasks ( task_id number primary key, project_id number constraint demo_tasks_project_fk references demo_projects ); insert into demo_projects values (1, 'Website'); insert into demo_tasks values (10, 1); commit; delete from demo_projects where project_id = 1;
Output:
Table DEMO_PROJECTS created. Table DEMO_TASKS created. 1 row inserted. 1 row inserted. Commit complete. Error starting at line : 9 In command - delete from demo_projects where project_id = 1 Error report - ORA-02292: integrity constraint (NIMBUS.DEMO_TASKS_PROJECT_FK) violated - child record found
Project 1 still has task 10, so it cannot be deleted. The message names the foreign key of the child table, DEMO_TASKS_PROJECT_FK.
Fix: Delete Children First, or Cascade
Example:
-- delete the children first, then the parent delete from demo_tasks where project_id = 1; delete from demo_projects where project_id = 1; rollback; -- or let the database do it: ON DELETE CASCADE alter table demo_tasks drop constraint demo_tasks_project_fk; alter table demo_tasks add constraint demo_tasks_project_fk foreign key (project_id) references demo_projects on delete cascade; delete from demo_projects where project_id = 1; select count(*) as tasks_left from demo_tasks;
Output:
1 row deleted.
1 row deleted.
Rollback complete.
Table DEMO_TASKS altered.
Table DEMO_TASKS altered.
1 row deleted.
TASKS_LEFT
_____________
0- Deleting the task first, then the project, works in one transaction; it is rolled back here to try the second way.
- With ON DELETE CASCADE on the foreign key, deleting the project deletes its tasks too.
Things to Know
- ON DELETE SET NULL keeps the children and clears their foreign key instead.
- Cascading deletes can remove far more rows than expected across several levels; use them only where children have no meaning without the parent.
- To find the child tables, look for constraints whose R_CONSTRAINT_NAME is the parent's primary key in USER_CONSTRAINTS.
Related Guides
- How to Use Foreign Key ON DELETE Actions in Oracle
- How to Fix ORA-02291: Integrity Constraint Violated - Parent Key Not Found in Oracle
Conclusion
ORA-02292 means a parent row still has children. Delete the children first, or define the foreign key with ON DELETE CASCADE or SET NULL when that is the rule you want.
