You are currently offline, waiting for your internet to reconnect
Questions about Windows 10? Click here

INF:Foreign Key Constraint Behavior of Nullable Composite Keys

This article was previously published under Q153370
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
A foreign key constraint may be satisfied under some conditions which mayseem unexpected or unusual. Specifically, if a composite foreign key hasbeen 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 keyconstraint will be satisfied. This is true even if there is not a row inthe related table to which any of the corresponding columns are matched.

Consider the following example involving tables t1 and t2, defined asfollows:
   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 intot2 values('B',null)."
More information
The ANSI standard, as explained in "A Guide to the SQL Standard," 3rdedition, by C.J. Date and Hugh Darwen, defines foreign key constraints asfollows:
check (fk MATCH [PARTIAL | FULL] (select ck from T))

where (a) fk is a row constructor corresponding to the column-commalistthat represents the foreign key, (b) ck is a select-item-commalistcorresponding to the column-commalist that represents the referencedcandidate 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 foreignkey definition (and likewise for FULL). In other words, SQL's definitionof "matching" for a foreign-key/candidate key pair is identical to itsdefinition of "matching" in the context of the match condition.

In cases such as SQL Server in which neither PARTIAL nor FULL are or can bespecified, 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 MATCHoption can be ignored if either the foreign key is not composite or ifevery component of the foreign key dis-allows nulls.
Properties

Article ID: 153370 - Last Review: 11/01/2013 23:31:00 - Revision: 4.0

  • Microsoft SQL Server 6.0 Standard Edition
  • Microsoft SQL Server 6.5 Standard Edition
  • kbnosurvey kbarchive KB153370
Feedback