I understand that in PostgreSQL if there is a multi-column FK from table
B to table
A and one or more of the FK columns in table
B are nullable then the FK is still considered as “honored” for the rows in
B where the value of such columns is indeed NULL with no check for the presence of a corresponding row in table
A (as primary key columns cannot be nullable):
However, is this PostgreSQL-specific or is it in the SQL standard? (experimenting with SQL Fiddle it seems that both MS SQL Server and MySQL behave in the same way).
This behavior (allowing
NULL values in FK columns) conforms to the SQL standard. The manual:
CREATE TABLEcommand conforms to the SQL standard, with exceptions listed below.
And there is no exception listed for FK constraints.
To disallow that some columns of a multi-column FK are NULL, use
MATCH FULL instead of the default
MATCH SIMPLE behavior. The manual:
MATCH FULLwill 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.
Or just define some or all column
NOT NULL. More here (as @ypercube commented):