A foreign key with multiple, nullable columns would still allow rows that contain invalid values if one of the columns is NULL. This can be prevented using the MATCH FULL option.
create table parent ( id_1 integer not null, id_2 integer not null, primary key (id_1, id_2) ); create table child ( child_id integer not null primary key, parent_id_1 integer null, parent_id_2 integer null, constraint fk_child_parent foreign key (parent_id_1, parent_id_2) references parent (id_1, id_2) );The above would allow the following INSERTs
insert into parent (id_1, id_2, values (1,1); insert into child (child_id, parent_id_1, parent_id_2) values (1, null, 42);
If the columns can't be defined as NOT NULL the solution is to define the foreign key with MATCH FULL
create table child ( child_id integer not null primary key, parent_id_1 integer null, parent_id_2 integer null, constraint fk_child_parent foreign key (parent_id_1, parent_id_2) references parent (id_1, id_2) MATCH FULL );
With the above definition, the two foreign key columns have to be both NULL or both have to reference a valid value from the parent table.