How to resolve ORA-02292 Error Message in Oracle database

Learn the purpose and how to get to the bottom of the ORA-02292 error message in Oracle.

Description

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

ORA-02292: integrity constraint violated – child report determined

Cause

You tried to DELETE a report from a parent table (as referenced by using a foreign key), however a report in the toddler desk exists.

Resolution

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

Option #1

This error often occurs when you have a parent-child relationship installed between two tables through a foreign key. You then have tried to delete a price into the guardian table, however the corresponding fee exists in the toddler table.

To right this problem, you need to replace or delete the fee into the infant table first and then you can delete the corresponding fee into the dad or mum 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 attempt 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 report from the dealer desk as follows:

DELETE from supplier
WHERE supplier_id = 1000;

You would receive the following error message:

Since the supplier_id fee of a hundred exists in the products, you want to first delete the report 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;