0

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

  • 1
    This is the (default) MATCH SIMPLE behaviour of foreign keys. Check the related question: dba.stackexchange.com/questions/58894/… – ypercubeᵀᴹ Nov 6 '14 at 13:37
  • @ypercube thanks; yet the links in the related question are all in postgresql documentation. My question is whether this is as per the SQL standard or not. – Marcus Junius Brutus Nov 6 '14 at 13:44
  • 1
    Yes, it's in the standard. If no one else answers, I'll add an answer later. – ypercubeᵀᴹ Nov 6 '14 at 13:45
2

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.