Почему мое ограничение проверки не останавливает эту нулевую вставку?

Может ли кто-нибудь объяснить, почему третья вставка (помеченная Данные запроса) в приведенном ниже коде разрешена SQL Server?

Насколько я могу судить, ограничение проверки должно разрешать только:

Code имеет значение null и System имеет значение null.
Code не является нулевым, а System - 1.

Моя первая мысль была ANSI NULLS, но установка их on или off не имела никакого значения.

Это упрощенный пример более крупной проблемы, найденной в нашем приложении (система была проверена на список чисел - IN(1, 2, etc.)). Мы заменили эту проверку внешним ключом (вместо IN) и новым контрольным ограничением, которое допускало либо, либо значение 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, [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

Ответ 1

Результатом оценки текущего ограничения для значений 123, NULL является Undefined.

([Code] IS NULL AND [System] IS NULL) оценивается как False
([Code] IS NOT NULL AND [System] IN (1, 2, 3)) оценивается как Undefined

Результат Undefined

Проверить ограничение

Ограничения CHECK отклоняют значения, которые оцениваются как FALSE. Поскольку null значения оцениваются в UNKNOWN, их присутствие в выражениях может переопределить ограничение.

Вы должны изменить свой чек на [System] IN (1, 2, 3) на ISNULL([System], 0) IN (1, 2, 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, 2, 3))   --Both not null ????
)

Ответ 2

Добро пожаловать в замечательную трехзначную логику 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 ????

Может показаться странным, как это определено, но оно согласуется с тем, как работают другие ограничения - например, ограничение внешнего ключа может иметь столбцы с нулевым значением, и если любой из этих столбцов имеет значение NULL, в ссылочной таблице не должно быть соответствующей строки.