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;

See also

Referential integrity, unique constraints, foreign keys.

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1740463282, '3.133.118.82', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/objects/tables/constraints/referential-integrity/foreign-key-unique-constraint(77): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78