Кто-нибудь может объяснить, почему третья вставка (помеченная Данные запроса ) в приведенном ниже коде разрешено SQL Server?
Насколько я могу судить, ограничение проверки должно разрешать только:
Код
имеет значение NULL, а System
- значение NULL. Код
не равен нулю, а Система
- 1
. Моя первая мысль была ANSI NULL
, но установка их на
или на
не имела никакого значения.
Это упрощенный пример более крупной проблемы, которую мы обнаружили в нашем приложении (система была проверена по списку номеров - IN (1, 2 и т. Д.)
). Мы заменили эту проверку внешним ключом (вместо 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