INF:Foreign Key Constraint Behavior of Nullable Composite Keys

 Last reviewed: April 8, 1997
Article ID: Q153370
The information in this article applies to:
  • Microsoft SQL Server versions 6.0 and 6.5

SUMMARY

A foreign key constraint may be satisfied under some conditions which may seem unexpected or unusual. Specifically, if a composite foreign key has been defined on columns that allow nulls, and at least one of the columns, upon the insert or update of a row, is set to null, then the foreign key constraint will be satisfied. This is true even if there is not a row in the related table to which any of the corresponding columns are matched.

Consider the following example involving tables t1 and t2, defined as follows: 

   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
The foreign key constraint on t2 can be met by the statement "insert into t2 values('B',null)."

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: 

   check (fk MATCH [PARTIAL | FULL] (select ck from T))
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: 

   The referential constraint is satisfied if and only if, for each
   row r2 of the referencing table, either (a) at least one component
   of r2.fk is null, or (b) T contains exactly one row, r1 say, such
   that the comparison condition "r2.fk = r1.ck" evaluates to true.
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.

Additional query words:
Version : 6.0 6.5
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 8, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.