multi-column FKs with NULL values [duplicate]

Posted on

Question :

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):

SQL Fiddle

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).

Answer :

This behavior (allowing NULL values in FK columns) conforms to the SQL standard. The manual:

The CREATE TABLE command 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 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.

Or just define some or all column NOT NULL. More here (as @ypercube commented):

Leave a Reply

Your email address will not be published. Required fields are marked *