ORA-02291: integrity constraint violated – parent key not found

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

ORA-02291: integrity constraint violated – parent key not found error occurs when we try to insert a row in a child table with a foreign key constraint and the related foreign key data is missing from parent table.
Let us create a scenario which causes ORA-02291 error.

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

Now while entering the data in EMPLOYEE_ADDRESS table the data should be already present in EMPLOYEE table.Otherwise foreign key violation will result.

SQL> insert into EMPLOYEE_ADDRESS values('abcd efgh',8);
ORA-02291: integrity constraint violated - parent key not found

Related Articles,Oracle/PLSQL: Foreign Keys | Oracle Referential Integrity
Oracle/PLSQL: Composite Primary Key
Oracle/PLSQL: Primary Key and Composite Primary Key

ALTER TABLE to ADD PRIMARY KEY in Oracle.
Technorati Tags:
, ,

0 Flares Twitter 0 Facebook 0 Google+ 0 LinkedIn 0 Buffer 0 Email -- Filament.io 0 Flares ×

4 thoughts on “ORA-02291: integrity constraint violated – parent key not found

  1. Pingback: Oracle Tables: Create Table as Select | SQL and PLSQL

  2. Pingback: Oracle/PLSQL: Create table with foreign key constraint | SQL and PLSQL

  3. Pingback: Best Tips On Getting Pregnant

Leave a Reply

Your email address will not be published. Required fields are marked *

Paged comment generated by AJAX Comment Page