任何人都可以解释sql Server允许下列代码中的第三个插入(标记为Query Data)的原因？
据我所知,支票约束应该只允许：
我的第一个想法是ANSI NULLS,但是将它们设置为关闭也没有任何区别.
这是我们在应用程序中发现的更大问题的简化示例(系统已针对数字列表(IN,1,2等)进行了检查).我们用一个外键(而不是IN)替换了这个检查和一个新的检查约束,允许null或null都为null;这样做阻止了第三个插入.
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,null);
--Bad data stopped
insert TestCheck (Code,1);
insert TestCheck (Code,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
解决方法
评估值123的当前约束的结果为NULL是未定义的.
>([Code] IS NULL AND [System] IS NULL)评估为False
>([Code] IS NOT NULL AND [System] IN(1,3))求值为“未定义”
结果未定义
CHECK constraints reject values that evaluate to FALSE. Because null
values evaluate to UNKNOWN,their presence in expressions may override
a constraint.
您应该将[系统] IN(1,3)的检查更改为ISNULL([系统],0)IN(1,3).
你的检查约束就变成了
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 ISNULL([System],0) IN (1,3)) --Both not null ????
)
