子表中的外键约束允许插入父表中不存在的值 [英] Foreign Key Constraint in child table allows to insert values that doesn't exists in the parent table

问题描述

我正在使用SQL Server2012。已在表上定义了外键约束。外键引用复合主键。当一列为 00000而另一列为空时，fk约束不起作用。父表不包含 00000。两个fk列均具有 varchar 数据类型。

I am using SQL Server 2012. I have defined a foreign key constraint on a table. The foreign key references composite primary key. The fk constraint does not work when one column has '00000' and other column is null. The parent table does not contain '00000'. Both of the fk columns have varchar data type.

下面是示例：

INSERT INTO XYZ
    ([BUSINESS_PARTNER_ID]
    ,[INDUSTRY_TYPE_CDE]
    ,[INDUSTRY_SUBTYPE_CDE])
VALUES
    (1,
    Null
    '00000')
GO

行业类型和行业子类型列是从另一个表中引用的。
以下是脚本：

The industry type and industry sub type column is referenced from another table. Below is the script:

ALTER TABLE [nfs].[xyz] WITH NOCHECK 
ADD CONSTRAINT [FK_BPMAIN__ITCDE_ISTCDE] 
FOREIGN KEY([INDUSTRY_TYPE_CDE], [INDUSTRY_SUBTYPE_CDE]) 
REFERENCES [nfs].[abc] ([INDUSTRY_TYPE_CDE], [INDUSTRY_SUBTYPE_CDE]) 
GO 

ALTER TABLE [nfs].[xyz] 
CHECK CONSTRAINT [FK_BPMAIN__ITCDE_ISTCDE] 
GO 

SQL Server插入时未给出任何错误。它将值插入子表中。根据我的理解，fk列可以为null或应包含父表中存在的值。

SQL Server does not give any error on insertion. It inserts the value in the child table. According to my understanding, the fk columns can be null or should contain values that exist in the parent table.

推荐答案

为什么当一个或多个列包含null时不检查fk约束？您能解释一下这种行为吗？

why does the fk constraint isn't checked when one or more column contains null? Can you please explain this behavior?

首先，我们有实用原因。使用索引维护和检查外键。为了使索引可用，我们需要知道索引内所有列的（要求）值。如果我们在（a，b）上有一个索引/ pk，而我们的外键值是（NULL，1），我们无法在索引中进行 seek 以确定是否存在任何 b 值为1的行。

First, we have the practical reasons. Foreign keys are maintained and checked using indexes. In order for an index to be usable, we need to know the (sought for) values of all columns within the index. If we have an index/pk on (a,b) and we have a foreign key value of (NULL,1), we cannot seek within the index in order to determine whether there is any row with a b value of 1. This would make the foreign key "expensive" to maintain.

但是第二，我们需要考虑一致性。对于单列的情况，这是相当没有争议的-如果FK列中有一个值，则在引用的列中需要有一个匹配的值。否则，如果FK列为 NULL ，则不检查约束。

But secondly, we need to consider consistency. For the single column case, its fairly uncontroversial - if you have a value in the FK column, then there needs to be a matching value in the referenced column. Otherwise, if the FK column is NULL then the constraint isn't checked.

但是，我们如何扩展这个到多列？上面的规则是什么 ？没有明显的解释，而是多个解释。上面的规则是如果 all 列均为非NULL，则检查约束还是如果 any 列非NULL，则检查约束？当仅考虑单个列时，这些规则是相同的。

But, how do we extend this to multiple columns? What is the rule above? There's not a single obvious interpretation, but instead multiple ones. Is the above rule "if all columns are non-NULL, then the constraint is checked" or "if any columns are non-NULL, then the constraint is checked"? These rules are identical when only a single column is under consideration.

您期望该规则是第二个规则，而实际上是第一条。明确记录：

You expected the rule to be the second one, when it is in fact the first. This is explicitly documented:

FOREIGN KEY约束可以包含空值；但是，如果复合FOREIGN KEY约束的任何列包含空值，那么将跳过对构成FOREIGN KEY约束的所有值的验证。为了确保验证了复合FOREIGN KEY约束的所有值，请在所有参与的列上指定NOT NULL。

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.

这篇关于子表中的外键约束允许插入父表中不存在的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！