1

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,409
  • 7
  • 30
  • 44

1 Answers1

3

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
  • 185,527
  • 28
  • 463
  • 633