Search notes:

Oracle: Referential integrity from a foreign key to a unique constraint

create table tq84_p (
    c1   varchar2(1) not null,
    c2   varchar2(1)     null,
    v    number,
    constraint tq84_p_uq unique(c1, c2)
);
 
create table tq84_c (
    c1,
    c2,
    v   number,
    constraint tq84_c_fk foreign key (c1, c2) references tq84_p(c1,c2)
);

The following insert statements run without problems:

begin
   insert into tq84_p values ('a', 'b', 1);
   insert into tq84_p values ('x', null, 2);
   
   insert into tq84_c values ('a', 'b', 9);
   insert into tq84_c values ('x', null, 8);
end;
/

This one throws ORA-02291: integrity constraint (RENE.TQ84_C_FK) violated - parent key not found

insert into tq84_c values ('b', 'd', 5);

The following insert statements run (contrary to my intution) also without problems:

begin
   insert into tq84_c values ('a', null, 7);
   insert into tq84_c values ('b', null, 6);
   insert into tq84_c values (null, null, 6);
   insert into tq84_c values (null, 'c', 6);
end;
/

Cleaning up:

drop table tq84_c;
drop table tq84_p;

Oracle: Referential integrity from a foreign key to a unique constraint

See also