ORA-02291: integrity constraint … violated

The ORA-02291: integrity constraint … violated - parent key not found error message is thrown if the value of a foreign key that was attempted to be inserted into a table was not found in the primary key.

Demonstration

The following simple demonstration will throw this error.

First, we need two tables with a foreign-primary key relationship:

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
);

The following statement throws ORA-02291: integrity constraint (RENE.FK) violated - parent key not found:

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

Cleaning up:

drop table tq84_F;
drop table tq84_P;

Determining parent and child table

The following statement helps to find the involved tables given the constraint name in the error message:

select
   pk.owner         pk_owner,
   pk.table_name    pk_table,
   fk.owner         fk_owner,
   fk.table_name    fk_table, 
   pk.status        pk_status,
   fk.status        fk_status
from
   all_constraints  pk                                                     join
   all_constraints  fk on pk.owner           = fk.r_owner            and
                          pk.constraint_name = fk.r_constraint_name
where
   fk.owner || '.' || fk.constraint_name = 'RENE.FK';

ORA-02291: integrity constraint … violated - parent key not found

Demonstration

Determining parent and child table

See also