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

Marcus Junius Brutus
  • 3,309
  • 7
  • 29
  • 44
  • 1
    This is the (default) MATCH SIMPLE behaviour of foreign keys. Check the related question: http://dba.stackexchange.com/questions/58894/match-full-vs-match-simple – ypercubeᵀᴹ Nov 06 '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 06 '14 at 13:44
  • 1
    Yes, it's in the standard. If no one else answers, I'll add an answer later. – ypercubeᵀᴹ Nov 06 '14 at 13:45

1 Answers1

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

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600