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.