0

This question already has an answer here:

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

marked as duplicate by Evan Carroll postgresql Jan 8 at 16:00

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

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