Use of FOREIGN key foreign in SQL Server version 2008r2

Original: Use of foreign key foreign in SQL Server 2008r2 version

1. In the database design process often want to let 2 tables to correlate and use to foreign thus strengthen the constraints between the 2 tables ()

Once a problem has not been understood, that is the value of the foreign key column, the normal case of the table column value can be null or satisfy the column of the type defined by the data, but the foreign key is not the same?

The answer is clearly not, what is the situation?

The official help document shows that the FOREIGN key constraint is not only linked to the PRIMARY key constraint of another table, it can also be defined as a UNIQUE constraint that references another table. The FOREIGN key constraint can contain null values, but if any of the columns that combine FOREIGN key constraints contain null values, the validation of all values that make up the FOREIGN key constraint is skipped. "

2. In order to verify the official statement, it was deliberately tested, so-called "experimentation is the only criterion to test truth."

First, insert a data into the usertype table--insert usertype (id,typename,createdatetime,modifydatetime) VALUES (NEWID (), ' System Administrator ', GETDATE ( ), GETDATE ())

The first time I inserted a piece of data into userinfo (from the table), but the foreign key here Usertypeid I didn't give the value, but the data was inserted successfully.

Second insertion ()

The foreign key I gave a value of "satisfy type", result insert failed

Last insertion ()

OK Insert success!

3. It is advisable to borrow the official words "theFOREIGN key constraint can contain null values, but if any of the columns that combine FOREIGN key constraints contain null values, the validation of all the values that make up the FOREIGN key constraint will be skipped ."