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: 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.
Learn the cause and how to resolve the ORA-02291 error message in Oracle.
Description
When you encounter an ORA-02291 error, the following error message will appear:
- ORA-02291: integrity constraint <constraint name> violated — parent key not found
Cause
You tried to reference a table using a unique or primary key, but the columns that you listed did not match the primary key, or a primary key does not exist for this table.
Resolution
The option(s) to resolve this Oracle error are:
Option #1
This error commonly occurs when you have a parent-child relationship established between two tables through a foreign key. You then have tried to insert a value into the child table, but the corresponding value does not exist in the parent table.
To correct this problem, you need to insert the value into the parent table first and then you can insert the corresponding value into the child table.
For example, if you had created the following foreign key (parent-child relationship).
CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier (supplier_id)
);
Then you try inserting into the products table as follows:
INSERT INTO products (product_id, supplier_id) VALUES (1001, 5000);
You would receive the following error message:
Since the supplier_id value of 5000 does not yet exist in the supplier table, you need to first insert a record into the supplier table as follows:
INSERT INTO supplier (supplier_id, supplier_name, contact_name) VALUES (5000, 'Microsoft', 'Bill Gates');
Then you can insert into the products table:
INSERT INTO products (product_id, supplier_id) VALUES (1001, 5000);
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.
Symptoms
When replicating or restoring Oracle database backup created using datapump, you receive foreign key constraint errors. The following appears in the atlassian-jira.log:
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-02298: cannot validate (WIKI_357.FKA898D4778DD41734) - parent keys not foundCause
This is usually caused by taking a database backup using datapump utility while the database is active, also known as a «hot dump».
Workaround
The specific entries causing the error to be thrown will be included in the logs.
The datapump file can be cleaned up using the following queries:
select <COLUMN_NAME> from <CHILD_TABLE> a where not exists (select <COLUMN_NAME> from
<PARENT_TABLE> where primary_key = a.<KEY_VALUE>);This will show you the row (or rows) that exist in the child table but don’t exist in the parent table. Once you have either (1)added rows to the parent table to match the child rows, or (2) removed the child rows that don’t have parents, run the import again, preferably in a development environment.
Resolution
- If possible, create the database dump while the database is not active.
- There are also some features in Oracle datapump that will allow you to avoid this kind of trouble when importing/exporting. Two specifically are FLASHBACK_TIME & FLASHBACK_SCN, which may be used to alleviate these problems in the future.
-
If it’s not possible to complete step 1, use the query in the Workaround section to clean up database foreign key constraint errors.