ORA-02291: integrity constraint violated – parent key not found error occurs when a foreign key value in the child table does not have a matching primary key value in the parent table, as stated by a foreign key constraint. You try to insert a row into a child table that does not have a corresponding parent row. The column value you supplied for the child table did not match the primary key in the parent table.
You try to insert or update a row in the child table. The value in the child table’s reference column should be available in the parent table’s primary key column. If the primary key column does not have a value, the row cannot be inserted or updated in the child table. The parent key’s integrity constraint was violated.
The value of the child table’s foreign key column should be the same as the value of the parent table’s primary key column. If the value does not exist in the parent table, an error ORA-02291: integrity constraint violated – parent key not found will be thrown.
Cause
A foreign key value has no matching primary key value.
Action
Delete the foreign key or add a matching primary key.
The Problem
When two tables in a parent-child relationship are created, a referential foreign key constraint is generated and enforces the relationship between the two tables. The value of the foreign key column in the child table is decided by the value of the primary key column in the parent table.
A value that is not available in the parent table cannot be inserted or updated in the child table. If you try to insert or update a value in the foreign key column of a child table, Oracle will throw the parent key integrity constraint violation error.
create table dept
(
id numeric(5) primary key,
name varchar2(100)
);
create table employee
(
id numeric(5) primary key,
name varchar2(100),
deptid numeric(5) references dept(id)
);
insert into employee values(1,'Yawin',1);Error
Error starting at line : 17 in command -
insert into employee values(1,'Yawin',1)
Error report -
ORA-02291: integrity constraint (HR.SYS_C0012551) violated - parent key not foundSolution 1
If the integrity constraint is violated, knowing the parent and child tables involved in the foreign key relationship is important. The parent and child table names, as well as the column names, may be retrieved using the integrity constraint name. The parent table, child table, parent column name, child column name, and integrity constraint name will be shown in the following sql query.
select r.constraint_name Foreign_key_constraint,
p.owner parent_owner, p.table_name parent_table, pc.column_name parent_column_name,
r.owner child_owner, r.table_name child_table, rc.column_name child_colum_name
from user_constraints p
join user_cons_columns pc on p.owner=pc.owner
and p.table_name=pc.table_name and p.constraint_name = pc.constraint_name
and p.constraint_type='P'
join user_constraints r on p.constraint_name=r.r_constraint_name and r.constraint_type='R'
join user_cons_columns rc on r.owner=rc.owner
and r.table_name=rc.table_name and r.constraint_name = rc.constraint_name
and r.constraint_type='R'
where r.constraint_name='SYS_C0012551'
order by p.owner, p.table_name, pc.column_name, rc.position;Output
Foreign_key_constraint | parent_owner |parent_table | parent_column_name |child_owner | child_table | child_colum_name
SYS_C0012548 HR DEPT ID HR EMPLOYEE DEPTIDSolution 2
The value you are trying to put into the child table reference column does not exist in the parent table. You must first enter the value that you intended to insert into the child table into the parent table. After inserting the value as a parent row, you may go back and enter it into the child table.
insert into dept values (1, 'sales');
insert into employee values(1,'Yawin',1)Output
1 row inserted.
1 row inserted.Solution 3
You are attempting to insert a row into a child table for which the primary key does not exist in the parent table. Before you enter a child, make sure you have a parent key for that child in the parent table.
insert into employee values(1,'Yawin',1)
insert into employee values(1,'Yawin',1)
Error report -
ORA-02291: integrity constraint (HR.SYS_C0012551) violated - parent key not found
insert into employee values(1,'Yawin',100) -- the value 100 exist in the dept table.I am creating a database that is trying to access values from a foreign key. I have created two following tables
CREATE TABLE Component(
ComponentID varchar2(9) PRIMARY KEY
, TypeID varchar2(9) REFERENCES TypeComponent(TypeComponentID)
)
INSERT INTO Component VALUES(192359823,785404309)
INSERT INTO Component VALUES(192359347,785404574)
INSERT INTO Component VALUES(192359467,785404769)
INSERT INTO Component VALUES(192359845,785404867)
INSERT INTO Component VALUES(192359303,785404201)
INSERT INTO Component VALUES(192359942,785404675)
CREATE TABLE TypeComponent (
TypeComponentID varchar2(9) PRIMARY KEY
, Type_Description varchar2(30) CONSTRAINT Type_Description
CHECK(Type_Description IN('Strap', 'Buckle', 'Stud')) NOT NULL
)
INSERT INTO TypeComponent VALUES(785404309, 'Strap')
INSERT INTO TypeComponent VALUES(785404574, 'Stud')
INSERT INTO TypeComponent VALUES(785404769, 'Buckle')
INSERT INTO TypeComponent VALUES(785404867, 'Strap')
INSERT INTO TypeComponent VALUES(785404201, 'Buckle')
INSERT INTO TypeComponent VALUES(785404675, 'Stud')
These are the two tables. Component and TypeComponent. Component is the parent entity to TypeComponent, and I am trying to run the following INSERT statement:
INSERT INTO Component VALUES(192359823,785404309)
but it is giving me the error
This is the session that I have so far in Oracle SQL dev
ORA-02291
The pleasure of Oracle software is the ease through which information can communicate across multiple tables in a database. Beyond having the ability to cleanly join tables and merge parameters, a number of devices in the software permit the access to and referencing of data from multiple tables, with unique features that allow you to create statements that can render formerly complex database issues with relatively little trouble.
Still, no user is perfect and no database can predict all of the potential errors that can arise during everyday use. In the realm of manipulating data across multiple data tables, a common error that you can encounter is the ORA-02291.
The Problem
ORA-02291 is typically accompanied with the message, “integrity constraint <constraint name> violated – parent key not found”. This means that you attempted to execute a reference to a certain table using a primary key. However, in the process of doing so, the columns that you specified failed to match the primary key. The error can also be triggered when referencing a primary key that does not exist for the table in question.
Before moving on, we should note a few things about primary keys. A primary key is a field or combination of fields that can distinctly denote a record. It can be established in either an ALTER TABLE or CREATE TABLE statement. A given table can only have one primary key, and none of the fields that populate the primary key can hold a null value. A primary key cannot exceed thirty-two columns.
Now that we have an understanding of primary keys, we can address the error at hand. Often, the error will arise when there is a parent-child relationship between two tables via a foreign key. A foreign key is a method to state that values in one particular table must exist in another. Typically the referenced table is a parent table, while the child table is where the foreign key emanates from. A primary key in a parent table will, most of the time, be referenced by a foreign key in the child table.
The ORA-02291 will be triggered when you attempt to insert a value into the child table (with the foreign key), but the value does not exist in the corresponding parent table. This violates the integrity of the referential relationship, prompting Oracle to issue an error message.
The Solution
In order to remedy this error, you will need to insert the value that you attempted to place in the child table into the parent table first. Once inserted as a parent row, you can go back and insert the value into the child table.
An Example
Let’s say that you first attempted to build the parent-child key relationship:
CREATE TABLE employees
( employee_id numeric (20) not null,
employee_name varchar2(75) not null,
supervisor_name varchar2(75),
CONSTRAINT employee_pk PRIMARY KEY (employee_id)
);
CREATE TABLE departments
( department_id numeric (20) not null,
employee_id numeric (20) >not null,
CONSTRAINT fk_employee
FOREIGN KEY (employee_id)
REFERENCES employee (employee_id)
);
From there, you attempt to place the following in the departments table:
INSERT INTO departments
(department_id, employee_id)
VALUES (250, 600) ;
You will receive an “ORA-02291: integrity constraint violated” error. Since the employee_id value of 600 does not already occur in the employees table, you will have to go back and insert the following into the employees table:
INSERT INTO employees
(employees_id, employees_name, supervisor_name)
VALUES (600);
You can then return to the departments table and finish the key relationship:
INSERT INTO departments
(department_id, employee_id)
VALUES (250, 600);
Looking forward
Working with multiple sets of data tables can seem daunting, and it can be easy to get the references mixed up. Luckily, Oracle alleviates a great deal of stress associated with working in multiple tables at once. Remaining aware of how you are cross-referencing information from table to table can provide a solid foundation to avoiding an error like the ORA-02291. Still, because this problem requires a little bit of background knowledge and coding to solve, it would be advised to speak with a licensed Oracle software consultant if you find yourself continually having issues addressing this error.
May 1, 2021
I got ” ORA-02291: integrity constraint (string.string) violated – parent key not found ” error in Oracle database.
ORA-02291: integrity constraint (string.string) violated – parent key not found
Details of error are as follows.
ORA-02291: integrity constraint (string.string) violated - parent key not found Cause: A foreign key value has no matching primary key value. Action: Delete the foreign key or add a matching primary key.
integrity constraint (string.string) violated – parent key not found
This ORA-02291 errors are related with the foreign key value has no matching primary key value.
To solve this error, you need to drop the foreign key or add a matching primary key.
Or firstly you need to insert the same value into the parent table, then you can insert the value into child table.
For example; I have 2 table which has relation between two table with EMPLOYEE_ID column as follows.
CREATE TABLE EMPLOYEE ( EMPLOYEE_ID numeric(10) not null, NAME varchar2(50) not null, LAST_NAME varchar2(50), CONSTRAINT emp_pk PRIMARY KEY (EMPLOYEE_ID) ); CREATE TABLE MANAGER ( ID numeric(10) not null, EMPLOYEE_ID numeric(10) not null, CONSTRAINT fk_EMPLOYEE FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE (EMPLOYEE_ID) );
I have inserted the following record.
INSERT INTO MANAGER (ID, EMPLOYEE_ID) VALUES (10, 63);
But I got this error, because 63 employee_id doesn’t exist in the employee table. So You need to insert this record to parent table, then you can insert the child table as follows.
INSERT INTO EMPLOYEE (EMPLOYEE_ID, NAME, LAST_NAME) VALUES (63, 'Mehmet ', 'Deveci ');
Then you can insert into the MANAGER table:
INSERT INTO MANAGER (ID, EMPLOYEE_ID) VALUES (10, 63);
Or you need to drop the emp_pk constraint.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
About 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 [email protected].- -Oracle DBA, SQL Server DBA, APPS DBA, Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için [email protected] a mail atabilirsiniz.
I keep getting this error when trying to populate my DB tables.
This is the code to create my tables:
CREATE table tbcustomer (
customerid char(4) not null constraint rg_customerid check (customerid between '1000' and '4999') constraint pk_customer primary key,
customername varchar2(40) not null,
customeraddress varchar2(50) null,
customercity varchar2(30) null,
customerstate char(2) null,
customerzip varchar2(10) null,
customercontact varchar2(30) null,
customerphone varchar2(12) null,
customeremail varchar2(50) null);
CREATE table tborder (
orderno number(11,0) not null constraint pk_order primary key,
orderdate date not null,
customerid char(4) not null constraint fk_customerid_tborder references tbcustomer on delete cascade);
CREATE table tbproduct (
productid char(3) not null constraint pk_product primary key constraint rg_productid check (productid between '100' and '999'),
productname varchar2(30) not null,
budgetsales number(4,0));
CREATE table tbvendor (
vendorid char(4) not null constraint pk_vendor primary key constraint rg_vendorid check (vendorid between '5000' and '9999'),
vendorname varchar2(25) not null,
vendoraddress varchar2(50) null,
vendorcity varchar2(30) null,
vendorstate char(2) null,
vendorzip varchar2(10) null);
CREATE table tbitem (
productid char(4) not null,
vendorid char(4) not null,
itemprice number(10,2) null constraint rg_itemprice check (itemprice>=0.00),
qoh number(8,0) not null,
constraint fk_productid_tbitem foreign key (productid) references tbproduct (productid),
constraint fk_vendorid foreign key (vendorid) references tbvendor (vendorid),
constraint pk_item primary key (productid, vendorid));
CREATE table tborderitem (
orderno number(11,0) not null constraint fk_orderno_tbborderitem references tborder (orderno) on delete cascade,
orderitemno char(2) not null,
productid char(3) not null,
vendorid char(4) not null,
quantity number(4,0) not null,
itemprice number(10,2) null,
constraint pk_orderitem primary key (orderno, orderitemno));
constraint fk_productid_vendorid foreign key (productid, vendorid) references tbitem (productid, vendorid) on delete cascade);
And this the code I use to populate my tables:
INSERT into tbcustomer values ('1123','Z Best','123 Main Street','Cambridge','MA','02139','Carol Jenkins','617-555-2222','jenskinssc@abc.com');
INSERT into tbcustomer values ('1234','Pop Shop','2233 Spring Street','Boston','MA','02114','Mandy Peters','617-344-1111','mpeters@def.com');
INSERT into tbcustomer values ('1667','Zoom','4545 Winter Street','Boston','MA','02112','James Hughes','617-433-3333','jhughes@zoomco.com');
INSERT into tbvendor values ('5100','Wesell','233 South Willow Street','Manchester','NH','03102');
INSERT into tbvendor values ('5200','Givin', '33 Harvard Place','Boston','MA','02211');
INSERT into tbvendor values ('5300','Z A List','4500 Summer Street','Quincy','MA','02161');
INSERT into tbproduct values ('100','Microwave','40');
INSERT into tbproduct values ('121','Toaster','30');
INSERT into tbproduct values ('434','Steamer','40');
INSERT into tbproduct values ('677','Coffee Maker','20');
INSERT into tborder values ('1','10-OCT-12','1667');
INSERT into tborder values ('2','12-OCT-12','1234');
INSERT into tborder values ('3','13-OCT-12','1667');
INSERT into tbitem values ('100','5100','55','22');
INSERT into tbitem values ('100','5200','66','20');
INSERT into tbitem values ('100','5300','70','35');
INSERT into tbitem values ('121','5100','12','20');
INSERT into tbitem values ('121','5300','15','15');
INSERT into tbitem values ('434','5100','18','35');
INSERT into tbitem values ('434','5200','25','25');
INSERT into tbitem values ('677','5100','40','20');
INSERT into tbitem values ('677','5200','46','30');
INSERT into tbitem values ('677','5300','48','20');
INSERT into tborderitem values ('1','01','100','5300','5','70');
INSERT into tborderitem values ('1','02','100','5100','5','55');
INSERT into tborderitem values ('1','03','121','5100','5','12');
INSERT into tborderitem values ('2','01','100','5200','10','65');
INSERT into tborderitem values ('2','02','677','5300','5','48');
INSERT into tborderitem values ('3','01','121','5100','5','12');
INSERT into tborderitem values ('3','02','677','5300','3','45');
INSERT into tborderitem values ('3','03','100','5100','2','55');
When I run the script I keep getting this error when inserting values to the tbitem table:
60-SQL>INSERT into tbitem values ('677','5300','48','20');
INSERT into tbitem values ('677','5300','48','20')
*
ERROR at line 1:
ORA-02291: integrity constraint (MQUIJANO.FK_PRODUCTID_TBITEM) violated -parent key not found
I also get the same error when trying to insert values into the tborderitem:
60-SQL>INSERT into tborderitem values ('1','01','100','5300','5','70');
INSERT into tborderitem values ('1','01','100','5300','5','70')
*
ERROR at line 1:
ORA-02291: integrity constraint (MQUIJANO.FK_PRODUCTID_VENDORID) violated -parent key not found
How can I fix this?