sql - 外键约束是否自动禁止空值？

Question

在我的事件表中，我有一个 StaffId 字段。这是 Staff 表(包含 StaffId)的 FK。在将数据插入 Activities 表时——如果 StaffId 为 null，它会抛出 FK 约束错误!？我在 SSMS 中勾选了“Alllow Nulls”框，为什么不是这样？

请注意，此记录是通过 DbCommand.ExecuteNonQuery 方法插入的。

另请注意:我刚刚使用 SSMS 运行了一条 Insert 语句来插入 NULL 值，并且成功了。可能这是一个更具体的 C# 问题!？

Answer 1

不，看这个http://msdn.microsoft.com/en-us/library/ms175464.aspx

A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. 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.

你可以在MySql中看到另一个例子，看下面的链接http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
可以看到可以SET NULL

SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.