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;