ORA-02292: integrity constraint violated – child record found

Hi,

Sometimes You can get ”  ORA-02292: integrity constraint violated – child record found ” error.

Details of error are as follows.

SQL> delete from hr.employees where EMPLOYEE_ID=100;
delete from hr.employees where EMPLOYEE_ID=100
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found

To solve this error, you should disable the related constraint or you need to firstly delete the record from referenced table.

Drop or disable the related constraint as follows.

SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY from hr.employees where EMPLOYEE_ID=100;

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 MEHMET SALIH         DEVECI                         30000



SQL> delete from hr.employees where EMPLOYEE_ID=100;
delete from hr.employees where EMPLOYEE_ID=100
*
ERROR at line 1:
ORA-02292: integrity constraint (HR.DEPT_MGR_FK) violated - child record found




SQL> alter table hr.departments  disable constraint DEPT_MGR_FK;

Table altered.



SQL> alter table hr.employees disable constraint EMP_MANAGER_FK;

Table altered.



SQL> delete from hr.employees where EMPLOYEE_ID=100;

1 row deleted.

SQL> commit;

Commit complete.



SQL> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY from hr.employees where EMPLOYEE_ID=100;

no rows selected

SQL>

Or you need to firstly delete the record from referenced table ( Child table data ) .

SQL> delete from hr.departments where EMPLOYEE_ID=100;

1 row deleted.

Do you want to learn more details about Delete operations and Oracle SQL Tutorial, then read the following post.

DELETE Statement in Oracle SQL | Oracle SQL Tutorials -11