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.

 

 69 views last month,  1 views today

Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply