ORA-02292: integrity constraint (…) violated

ORA-02292: integrity constraint (…) violated - child record found

Trying to delete a record in a table with a primary key whose value is still referenced in a table with a foreign key causes Oracle to throw the ORA-02292 error.

Demonstration

create table tq84_P (
   id  number(6),
   val varchar2(10),
   --
   constraint pk primary key (id)
);

create table tq84_F (
    id_p,
    txt    varchar2(20),
    --
    constraint fk foreign key (id_p) references tq84_P
);

insert into tq84_P values (1, 'one');
insert into tq84_F values (1, 'xyz');

Following statement throws ORA-02292: integrity constraint (RENE.FK) violated - child record found because TQ84_F has a record where id_p = 1:

delete from tq84_P where id = 1;

The following statement can be used to find the name and owner of the table with the primary key:

select
   pk.owner          pk_owner,
   pk.table_name     pk_table_name
from
   dba_constraints fk join
   dba_constraints pk on pk.owner            = fk.r_owner and
                         pk.constraint_name  = fk.r_constraint_name
where
   fk.owner           = 'RENE' and
   fk.constraint_name = 'FK'
;

Cleaning up:

drop table tq84_F;
drop table tq84_P;

ORA-02292: integrity constraint (…) violated - child record found

Demonstration

See also