Почему мое ограничение проверки не останавливает эту нулевую вставку?
Может ли кто-нибудь объяснить, почему третья вставка (помеченная Данные запроса) в приведенном ниже коде разрешена 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, в ссылочной таблице не должно быть соответствующей строки.
