Oracle: ORA-02292 Error Message

ORA-02292 Error Message

Learn the cause and how to resolve the ORA-02292 error message in Oracle.

Description

When you encounter an ORA-02292 error, the following error message will appear:

• ORA-02292: integrity constraint <constraint name> violated - child record found

Cause

You tried to DELETE a record from a parent table (as referenced by a foreign key), but a record in the child table exists.

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 delete a value into the parent table, but the corresponding value exists in the child table.

To correct this problem, you need to update or delete the value into the child table first and then you can delete the corresponding value into the parent 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 supplier
(supplier_id, supplier_name, contact_name)
VALUES (1000, 'Microsoft', 'Bill Gates');

INSERT INTO products
(product_id, supplier_id)
VALUES (50000, 1000);

Then you tried to delete the record from the supplier table as follows:

DELETE from supplier
WHERE supplier_id = 1000;

You would receive the following error message:

Since the supplier_id value of 100 exists in the products, you need to first delete the record from the products table as follows:

DELETE from products
WHERE supplier_id = 1000;

Then you can delete from the supplier table:

DELETE from supplier
WHERE supplier_id = 1000;