INF:Foreign Key Constraint Behavior of Nullable Composite Keys

   t1:   (col1, col2)
      primary key (col1,col2)

   t2:   (col1 null, col2 null)
      foreign key (col1,col2) references t1

   If t1 contains one row:

      col1  col2
      ----  ----
      A  2
				

MORE INFORMATION

The ANSI standard, as explained in "A Guide to the SQL Standard," 3rd edition, by C.J. Date and Hugh Darwen, defines foreign key constraints as follows:

where (a) fk is a row constructor corresponding to the column-commalist that represents the foreign key, (b) ck is a select-item-commalist corresponding to the column-commalist that represents the referenced candidate key, (c) T is the base table that contains that candidate key, and (d) PARTIAL is specified if and only if PARTIAL appears in the foreign key definition (and likewise for FULL). In other words, SQL's definition of "matching" for a foreign-key/candidate key pair is identical to its definition of "matching" in the context of the match condition.

In cases such as SQL Server in which neither PARTIAL nor FULL are or can be specified, Date and Darwen go on to say:

Date and Darwen add a footnote that states, in effect, that the MATCH option can be ignored if either the foreign key is not composite or if every component of the foreign key dis-allows nulls.