null value in foreign key

sql sql-server null foreign-keys referential-integrity
Ziyang Zhang picture Ziyang Zhang · Jun 4, 2012 · Viewed 7.7k times · Source

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.

Answer

Tony picture Tony · Jun 4, 2012

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.

Related questions

How to use NULL or empty string in SQL

I would like to know how to use NULL and an empty string at the same time in a WHERE clause in SQL Server. I need to find records that have either null values or an empty string. Thanks.

Read More
Not equal <> != operator on NULL

Could someone please explain the following behavior in SQL? SELECT * FROM MyTable WHERE MyColumn != NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn <> NULL (0 Results) SELECT * FROM MyTable WHERE MyColumn IS NOT NULL (568 Results)

Read More
NULL values inside NOT IN clause

This issue came up when I got different records counts for what I thought were identical queries one using a not in where constraint and the other a left join. The table in the not in constraint had one null …

Read More