How to resolve ORA-02291 Error Message in Oracle database

Learn the cause and how to get to the bottom of the ORA-02291 error message in Oracle.

Description

When you stumble upon an ORA-02291 error, the following error message will appear:

ORA-02291: integrity constraint violated – parent key now not observed

Cause

You tried to reference a desk the use of a special or main key, but the columns that you listed did now not match the essential key, or a predominant key does now not exist for this table.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

This error many times occurs when you have a parent-child relationship mounted between two tables through a overseas key. You then have tried to insert a fee into the infant table, but the corresponding price does no longer exist in the mum or dad table.

To right this problem, you want to insert the price into the mother or father desk first and then you can insert the corresponding price into the toddler table.

For example, if you had created the following overseas 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 merchandise table as follows:

INSERT INTO products
(product_id, supplier_id)
VALUES (1001, 5000);

You would receive the following error message:

Since the supplier_id price of 5000 does not yet exist in the dealer table, you need to first insert a record into the provider desk 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);