‘ORA-02292: integrity constraint violated – child record found‘ error occurs when we try to delete a row from a parent table which is having a child table with foreign key constraint and the corresponding data exists in the child table.
Let us create a scenario which causes ORA-02292 error.
Foreign Key holds the reference to another table column value.It is also known as references constraint.
A foreign key means that values in one table must also appear in another table.A references constraint is only applied at SQL ‘insert’ and ‘delete’ times.
Let us create an EMPLOYEE table which holds the employee information like name ,id and salary.
Here emp_id is the primary key column.
SQL> create table EMPLOYEE( name VARCHAR2(50), emp_id NUMBER, salary NUMBER(8,2), CONSTRAINT EMPLOYEE_ID_PK PRIMARY KEY (emp_id)); Table created
Now create an Employee address table which holds the address details of the employees.The address table is linked to EMPLOYEE table through the foreign key emp_id;
SQL> create table EMPLOYEE_ADDRESS( address varchar2(200), emp_id number, CONSTRAINT EMP_ADD_FK FOREIGN KEY (emp_id)REFERENCES EMPLOYEE(emp_id)); Table created
Now let us insert one record in EMPLOYEE table.
SQL> insert into EMPLOYEE values(' ABCD',1,12345); 1 row inserted
Now let us add address for this particular employee in EMPLOYEE_ADDRESS table.
SQL> insert into EMPLOYEE_ADDRESS values('abcd efgh',1); 1 row inserted
Also if we delete a row from the EMPLOYEE table and if a reference exists in EMPLOYEE_ADDRESS table oracle will throw an error showing reference integrity dependency.
SQL> delete from EMPLOYEE where emp_id =1; ORA-02292: integrity constraint (TEST.EMP_ADD_FK) violated - child record found
So you need to delete the row from the child table first and then delete the record from the parent table.
SQL> delete from EMPLOYEE_ADDRESS where emp_id=1; 1 row deleted SQL> delete from EMPLOYEE where emp_id =1; 1 row deleted
Alternatively you can declare the ‘ON DELETE CASCADE‘ option while creating foreign key constraints.
Related Articles,ORA-02449: unique/primary keys in table referenced by foreign
ORA-02292: integrity constraint violated – child record found
ORA-02291: integrity constraint violated – parent key not Found
Oracle/PLSQL: Foreign Keys | Oracle Referential Integrity
Oracle/PLSQL: Composite Primary Key
Oracle/PLSQL: Primary Key and Composite Primary Key
ORA-02292, integrity constraint violated, child record found
Pingback: Oracle Tables: Foreign Keys with ON DELETE CASCADE option | SQL and PLSQL
Thanks ! very usefull
thanks it very useful for me.