INF:Foreign Key Constraint Behavior of Nullable Composite Keys

ID: Q153370

The information in this article applies to:

Microsoft SQL Server versions 6.0, 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.

Keywords :
Version : 6.0 6.5
Platform : WINDOWS
Issue type :