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.

Back to the SQL Feature Comparison