sql-server - 为什么我的检查约束不能阻止这个空插入？

Question

任何人都可以解释为什么 SQL Server 允许下面代码中的第三次插入(标记为查询数据)吗？

据我所知，检查约束应该只允许:

• Code 为 null，System 为 null。
• Code 不为 null，并且 System 为 1。

我的第一个想法是ANSI NULLS，但将它们设置为打开或关闭没有什么区别。

这是我们在应用程序中发现的一个较大问题的简化示例(系统根据数字列表进行检查 - IN(1, 2, etc.))。我们用一个外键(而不是 IN)和一个新的检查约束替换了这个检查，该约束允许两个都为空或者两个不为空；这样做可以防止第三次插入。

IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_TestCheck]') AND parent_object_id = OBJECT_ID(N'[dbo].[TestCheck]'))
    ALTER TABLE [dbo].[TestCheck] DROP CONSTRAINT [CK_TestCheck]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestCheck]') AND type in (N'U'))
    DROP TABLE [dbo].[TestCheck]
GO

SET ANSI_NULLS ON
GO

CREATE TABLE TestCheck(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Code] [varchar](50) NULL,
    [System] [tinyint] NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC))
GO

ALTER TABLE [dbo].[TestCheck] WITH CHECK ADD CONSTRAINT [CK_TestCheck] CHECK
(
    ([Code] IS NULL AND [System] IS NULL)   --Both null
    OR
    ([Code] IS NOT NULL AND [System] = 1)   --Both not null ????
)
GO

ALTER TABLE [dbo].[TestCheck] CHECK CONSTRAINT [CK_TestCheck]
GO

--Good Data
insert TestCheck (Code, [System]) Values(null, null);
insert TestCheck (Code, [System]) Values('123', 1);

--Query Data
insert TestCheck (Code, [System]) Values('123', null);

--Bad data stopped
insert TestCheck (Code, [System]) Values(null, 1);
insert TestCheck (Code, [System]) Values('123', 4);

select * from TestCheck
Where
    case when
    (
        ([Code] IS NULL AND [System] IS NULL)           --Both null
        OR
        ([Code] IS NOT NULL AND [System] in (1, 2, 3))  --Both not null ????
    )
    then 0 else 1 end
     = 1

Answer 1

欢迎来到 SQL 精彩的三值逻辑。您可能知道也可能不知道，与 null 的任何标准比较的结果都不是 TRUE 或 FALSE，而是 UNKNOWN .

在 WHERE 子句中，整个子句的计算结果必须为 TRUE。

在 CHECK 约束中，整个约束的计算结果必须不为 FALSE。

所以，我们有:

([Code] IS NULL AND [System] IS NULL)   --Both null
OR
([Code] IS NOT NULL AND [System] = 1)   --Both not null ????

这变成(对于查询数据):

(FALSE AND TRUE)
OR
(TRUE AND UNKNOWN)

任何一侧或另一侧具有 UNKNOWN 的运算符都会计算为 UNKNOWN，因此总体结果为 UNKNOWN。这不是 FALSE，因此评估检查约束是成功的。

<小时/>

如果您希望 System 不为空，那么将其添加为额外的明确要求对我来说是最清楚的。

([Code] IS NULL AND [System] IS NULL)   --Both null
OR
([Code] IS NOT NULL AND [System] IS NOT NULL AND [System] = 1)   --Both not null ????

<小时/>

它的定义方式可能看起来有点奇怪，但它与其他约束的工作方式是一致的 - 例如外键约束可能具有可为空的列，如果这些列中的任何一个为空，则引用的表中不必有匹配的行。