null value in foreign key

Question

I copied the following paragraph from http://msdn.microsoft.com/en-us/library/ms175464(v=sql.105).aspx

A FOREIGN KEY constraint can contain null values; however, if any column of a composite FOREIGN KEY constraint contains null values, verification of all values that make up the FOREIGN KEY constraint is skipped. To make sure that all values of a composite FOREIGN KEY constraint are verified, specify NOT NULL on all the participating columns.

Could someone provide me with a simple example to help me understand the above? What verification the foreign key constraint will do? I think this has something to do with referential integrity.

Thanks a lot.

One more reason not to use composite PKS. – HLGEM Jun 04 '12 at 22:22 — HLGEM, Jun 04 '12 at 22:22

score 2 · Answer 1 · answered Jun 04 '12 at 20:53

2

You shouldn't need an example if you think of it in another way.

If any of the fields in a composite foreign key are NULL then none of the other fields are checked for integrity until all fields of the key are provided.

answered Jun 04 '12 at 20:53

Tony

9,672
3
47
75

score 2 · Answer 2 · answered Jun 04 '12 at 20:54

It is saying that if you have a composite foreign key using the two columns below, the relationship will not be enforced (because Name is NULL for OtherId 2).

OtherId  Name
-------  -------
1        Abe
2        NULL
3        Jim
4        Bob

Since the relationship is not enforced, you cannot enforce referential integrity. If you specify that neither of the columns can have null values then you will never run into this situation and referential integrity will be maintained.

score 1 · Answer 3 · answered Jun 04 '12 at 20:53

Here's an example:

create table t1 (id1 int, id2 int,
    constraint PK_T1 primary key (id1, id2))
create table t2 (id1 int, id2 int)

insert t2 values (1,null)

alter table t2 add constraint FK_T2_T1 foreign key (id1, id2) references t1(id1, id2)

The last line should cause an error. After all, t1 is empty, so the single row in t2 can't possibly refer to anything. But because one of the columns contains a null value, verification is skipped.

null value in foreign key

3 Answers3