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