Composite foreign key that is optional

Question

I have a table with a composite foreign key like so:

CREATE TABLE a
(
  a1 INTEGER,
  a2 INTEGER,
  a3 INTEGER,
  a4 INTEGER,
  -- other columns
  FOREIGN KEY (a1, a2, a3, a4) REFERENCES b
);

The foreign key may or may not exists, therefore a1 to a4 may be NULL, however if so, they should be NULL all at the same time. Is there a better way to specify such an "optional composite foreign key" than to add a (rather verbose) constraint like so?

  CHECK ((a1 IS NULL) = (a2 IS NULL) AND 
         (a3 IS NULL) = (a4 IS NULL) AND 
         (a1 IS NULL) = (a3 IS NULL))

score 2 · Accepted Answer · answered Aug 20 '16 at 06:14

You can try with:

CREATE TABLE a
(
  a1 INTEGER,
  a2 INTEGER,
  a3 INTEGER,
  a4 INTEGER,
  -- other columns
  FOREIGN KEY (a1, a2, a3, a4) REFERENCES b MATCH FULL
);

From the manual:

A value inserted into the referencing column(s) is matched against the values of the referenced table and referenced columns using the given match type. There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE (which is the default). MATCH FULL will not allow one column of a multicolumn foreign key to be null unless all foreign key columns are null; if they are all null, the row is not required to have a match in the referenced table.

Composite foreign key that is optional

1 Answers1