ORA-02292"integrity constraint violated -

Question

I have an application that should delete orders from the order table and insert the details of the deleted order into two tables: One contains the details of the order and the second one contains the details of the odetails. The problem comes from the table DELETE_ORDERS_D as it throws the following exception:

        02292. 00000 - "integrity constraint (%s.%s) violated - child record  
                        found"
        *Cause:    attempted to delete a parent key value that had a foreign
                        dependency.

Orders

      create table orders (
       ono      number(5) not null primary key,
       cno      number(5) references customers,
       eno      number(4) references employees,
       received date,
       shipped  date);

Order details

     create table odetails (
     ono      number(5) not null references orders,
     pno      number(5) not null references parts,
     qty      integer check(qty > 0),
     primary key (ono,pno));

Parts

     create table parts(
     pno      number(5) not null primary key,
     pname    varchar2(30),
     qoh      integer check(qoh >= 0),
     price    number(6,2) check(price >= 0.0),
     olevel   integer);

Sample data

     insert into orders values
       (1020,1111,1000,'10-DEC-11','12-DEC-11');
     insert into orders values
       (1021,1111,1000,'12-JAN-12','15-JAN-12');
     insert into orders values
       (1022,2222,1001,'13-FEB-12','20-FEB-12');
     insert into orders values
       (1023,3333,1000,'12-MAR-12',null);
     insert into odetails values
       (1020,10506,1);
     insert into odetails values
       (1020,10507,1);
     insert into odetails values
       (1020,10508,2);
     insert into odetails values
       (1020,10509,3);
     insert into odetails values
       (1021,10601,4);
     insert into odetails values
       (1022,10601,1);
     insert into odetails values
       (1022,10701,1);
     insert into odetails values
       (1023,10800,1);
     insert into odetails values
       (1023,10900,1);
     insert into parts values
       (10506,'Land Before Time I',200,19.99,20);
    insert into parts values
       (10507,'Land Before Time II',156,19.99,20);
    insert into parts values
       (10508,'Land Before Time III',190,19.99,20); 
    insert into parts values
       (10509,'Land Before Time IV',60,19.99,20);
    insert into parts values
       (10601,'Sleeping Beauty',300,24.99,20);
    insert into parts values
       (10701,'When Harry Met Sally',120,19.99,30);
    insert into parts values
       (10800,'Dirty Harry',140,14.99,30);
    insert into parts values
       (10900,'Dr. Zhivago',100,24.99,30);

Table generates the error is bellow

    CREATE TABLE DELETE_ORDERS_D (
       ONO      number(5) not null references orders,
       PNO      number(5) not null references parts,
       QTY     integer check(qty > 0),
       CONSTRAINT DEL_ODETAILS_pk PRIMARY KEY (ONO,PNO));

Constraint optimization problem

select * from all_constraints
     where owner = 'SCOTT'
     and constraint_name = 'SYS_C007539';


          CONSTRAINT_TYPE           TABLE_NAME                
        ------------------        -----------------
                R                  DELETE_ORDERS_D

Could you please help me to solve this!

Answer 1

History tables such as DELETE_ORDERS_D can't have foreign key constraints which reference the actual data tables (ORDERS and PARTS, in this case) because when you delete the corresponding rows in ORDERS and PARTS it will cause precisely the error you're seeing. Drop the foreign key constraints on DELETE_ORDERS_D.ONO and PNO.

Best of luck.

Answer 2

If you are getting such errors then find the table which has to be deleted first by using below query:

 SELECT * FROM dba_constraints
 WHERE constraint_name = 'FK_WMINVN_LOCNHDR'; 

Answer 3

As said previously by bob jarvis history tables shouldnt be having constriants referencing to the actual data.

However to resolve you issue you can write on delete cascade along with your foreign key constraints. Whenever you will issue a delete a record on the parent table this will automatically delete all the child record associated with it

Alter the foreign key constraints on the odetails. But for that you need to drop the existing foreign key constraints in odetails table.

alter table odetails add constraint odetails_ono_fk
foreign key (ono) references orders(ono) on delete cascade;