Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
Young Mockin
Member

Откуда:
Сообщений: 75
Есть таблица с полем Success типа BIT, поддерживающее возможность хранения NULL значений.
И еще одно поле ActionDate этой же таблицы, тоже нулябельное, типа DATETIME.
Идея такова, что либо оба поля не заполнены, либо оба заполнены при этом Success - истина, таким образом,
имеем настроенный CONSTRAINT ... CHECK (ActionDate IS NULL AND Success IS NULL OR ActionDate IS NOT NULL AND Success = 1)
который, однако, пропускает ситуцию с заполненным ActionDate и незаполненным Success (но не пропускает хранение значений 0 для ActionDate, если заполнен ActionDate).
Пришлось переписывать проверку таким образом: (ActionDate IS NULL AND Success IS NULL OR ActionDate IS NOT NULL AND ISNULL(Success, 0) = 1)
тогда, само собой, NULL записать в Success, если указан ActionDate, сервер уже не дает.
С чего это сервер считает в констрейнте, что NULL = 1 есть истина?

Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
27 янв 12, 09:59    [11980986]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
Glory
Member

Откуда:
Сообщений: 104751
Young Mockin
имеем настроенный CONSTRAINT ... CHECK (ActionDate IS NULL AND Success IS NULL OR ActionDate IS NOT NULL AND Success = 1)
который, однако, пропускает ситуцию с заполненным ActionDate и незаполненным Success (но не пропускает хранение значений 0 для ActionDate, если заполнен ActionDate).

Скобки нужно правильно ставить
((ActionDate IS NULL AND Success IS NULL) OR (ActionDate IS NOT NULL AND Success = 1))
27 янв 12, 10:11    [11981052]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Young Mockin
С чего это сервер считает в констрейнте, что NULL = 1 есть истина?
Да, интересно:
create table #t
(
	ActionDate int, 
	Success int,
	CHECK (null = 1)
)
27 янв 12, 10:11    [11981053]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Glory
Скобки нужно правильно ставить
((ActionDate IS NULL AND Success IS NULL) OR (ActionDate IS NOT NULL AND Success = 1))
Тоже так полдумал, но:
create table #t
(
	ActionDate int, 
	Success int,
	CHECK ((ActionDate IS NULL AND Success IS NULL) OR (ActionDate IS NOT NULL AND Success = 1))
)
insert #t values(1, null)

select * from #t
go
drop table #t
27 янв 12, 10:12    [11981060]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Young Mockin,

всё дело в том, что CHECK CONSTRAINT не пропускает значения, для которых выражение возвращает FALSE.
Если в условии, которое Вы показали,
ActionDate IS NULL AND Success IS NULL OR ActionDate IS NOT NULL AND Success = 1
Sucsess IS NULL, то получается
ActionDate IS NULL AND NULL IS NULL OR ActionDate IS NOT NULL AND NULL = 1
===>
FALSE AND TRUE OR TRUE AND UNKNOWN
===>
FALSE OR UNKNOWN
===>
UNKNOWN <> FALSE ===> пропускаем!
27 янв 12, 10:13    [11981064]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
alexeyvg
Тоже так полдумал, но:

select @@VERSION
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) 
	Jun 17 2011 00:54:03 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
27 янв 12, 10:14    [11981071]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
Young Mockin
Member

Откуда:
Сообщений: 75
Glory,

ну, вобще то, я скобки так и ставил. Только после выполнения alter таблицы, сам сервер записал выражение именно в таком виде, в каком я процитировал его в первом сообщении. Сервер сам же их и убирает.
Да и согласно порядку вычислений, скобки в таком виде не влияют на результат.
27 янв 12, 10:15    [11981077]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
iap
всё дело в том, что CHECK CONSTRAINT не пропускает значения, для которых выражение возвращает FALSE.
Вы хотели сказать не FALSE, а UNKNOWN?

Да, видимо так...
27 янв 12, 10:16    [11981080]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Предикаты же пропускают только TRUE.
Поэтому NULL, когда он приводит к результату UNKNOWN,
они не пропускают!

Вот такое вот отличие логических выражений в предикатах и констрейнтах
благодаря троичной логике!
27 янв 12, 10:16    [11981085]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
alexeyvg
iap
всё дело в том, что CHECK CONSTRAINT не пропускает значения, для которых выражение возвращает FALSE.
Вы хотели сказать не FALSE, а UNKNOWN?

Да, видимо так...
Нет. Я хотел сказать "FALSE". Именно FALSE CHECK CONSTRAINT не пропускает
Хорошо об этом написано у Ицика Бен-Гана, кстати.
27 янв 12, 10:17    [11981096]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Можно сказать, что предикаты и констрейнты ведут себя так, будто логика двоичная (TRUE и FALSE).
При этом UNKNOWN как бы попадает в категорию "прочие значения, которые не проверяем"
Будь она действительно двоичной, предикаты и констрейнты были бы противоположны по смыслу,
и это было бы совершенно логично.
27 янв 12, 10:22    [11981122]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
iap
alexeyvg
пропущено...
Вы хотели сказать не FALSE, а UNKNOWN?

Да, видимо так...
Нет. Я хотел сказать "FALSE". Именно FALSE CHECK CONSTRAINT не пропускает
Хорошо об этом написано у Ицика Бен-Гана, кстати.
iap
Предикаты же пропускают только TRUE.
Поэтому NULL, когда он приводит к результату UNKNOWN,
они не пропускают!

"Не пропускают" - для меня это синоним "запрещают вставить запись"...
То есть запись можно вставить, если результат равен TRUE, а в остальных случаях констрейн запрещает изменения.

Я всегда думал так...
27 янв 12, 10:32    [11981175]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
iap
Можно сказать, что предикаты и констрейнты ведут себя так, будто логика двоичная (TRUE и FALSE).
При этом UNKNOWN как бы попадает в категорию "прочие значения, которые не проверяем"
Будь она действительно двоичной, предикаты и констрейнты были бы противоположны по смыслу,
и это было бы совершенно логично.
Да, получается так...

Ищу щас какое то описание в BOL, пока не нашёл...
27 янв 12, 10:32    [11981183]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
alexeyvg
iap
Можно сказать, что предикаты и констрейнты ведут себя так, будто логика двоичная (TRUE и FALSE).
При этом UNKNOWN как бы попадает в категорию "прочие значения, которые не проверяем"
Будь она действительно двоичной, предикаты и констрейнты были бы противоположны по смыслу,
и это было бы совершенно логично.
Да, получается так...

Ищу щас какое то описание в BOL, пока не нашёл...
Это не специфическое поведение MSSQL.
Это стандарты такие.
Говорю же, откройте Бен-Гана и найдите у него. Это достаточный авторитет?
"Не пропускает"="Не позволяет вставить". Это Вы правильно заметили.
Таким образом, констрейнт говорит: "Не дам вставить FALSE! Аааа! У Вас UNKNOWN? Тогда пожалуйста!"
27 янв 12, 10:41    [11981249]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
http://msdn.microsoft.com/ru-ru/library/ms191504(v=SQL.100).aspx
27 янв 12, 11:06    [11981368]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
Young Mockin
Member

Откуда:
Сообщений: 75
iap
Предикаты же пропускают только TRUE.
Поэтому NULL, когда он приводит к результату UNKNOWN,
они не пропускают!

Вот такое вот отличие логических выражений в предикатах и констрейнтах
благодаря троичной логике!


Предикат это в частном случае WHERE в SELECT?
Вот именно это и смутило больше всего: то, что для WHERE UNKNOWN будет FALSE, а для CONSTRAINT тот же самый UNKNOWN по сути TRUE.
Выходит, это правильное поведение стандарта?
Выходит, одним условием Success = 1 не обойтись, ставя либо ISNULL, либо дополнительно Success IS NOT NULL... чудны дела твои SQL.
27 янв 12, 11:18    [11981429]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
Встретилось тут
Itzik Ben-Gan
UNKNOWN logical results and NULLs are treated inconsistently in different elements of
the language. For example, all query fi lters (ON, WHERE, and HAVING) treat UNKNOWN
like FALSE. A row for which a fi lter is UNKNOWN is eliminated from the result set. On
the other hand, an UNKNOWN value in a CHECK constraint is actually treated like TRUE.
Suppose you have a CHECK constraint in a table to require that the salary column be
greater than zero. A row entered into the table with a NULL salary is accepted because
(NULL > 0) is UNKNOWN and treated like TRUE in the CHECK constraint.
27 янв 12, 14:34    [11983541]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
denis2710
Встретилось тут
Itzik Ben-Gan
UNKNOWN logical results and NULLs are treated inconsistently in different elements of
the language. For example, all query fi lters (ON, WHERE, and HAVING) treat UNKNOWN
like FALSE. A row for which a fi lter is UNKNOWN is eliminated from the result set. On
the other hand, an UNKNOWN value in a CHECK constraint is actually treated like TRUE.
Suppose you have a CHECK constraint in a table to require that the salary column be
greater than zero. A row entered into the table with a NULL salary is accepted because
(NULL > 0) is UNKNOWN and treated like TRUE in the CHECK constraint.
Я об этом и говорил.
Только у меня на русском языке бумажная книжка
27 янв 12, 15:34    [11984169]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
iap
denis2710
Встретилось тут
пропущено...
Я об этом и говорил.
Только у меня на русском языке бумажная книжка

Везет а у меня электронная и на английском :)
Взято из Itzik Ben-Gan Inside Microsoft® SQL Server® 2008:T-SQL Querying.На русском я ее не видел :(
27 янв 12, 15:45    [11984255]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
denis2710
На русском я ее не видел :(


Microsoft SQL Sever 2008. Основы T-SQL

Картинка с другого сайта.

Глава 2. Однотабличные запросы
- Значение NULL - стр. 69
27 янв 12, 19:00    [11985989]     Ответить | Цитировать Сообщить модератору
 Re: Проверка на значение ALLOWS NULL битового поля в CONSTRAINT ... CHECK  [new]
denis2710
Member

Откуда: Москва
Сообщений: 3384
Knyazev Alexey,я рад,но это та книга
Найдите 10 отличий
Inside Microsoft® SQL Server® 2008:T-SQL Querying
и
Microsoft SQL Server 2008: T-SQL Fundamentals aka Microsoft SQL Sever 2008. Основы T-SQL
28 янв 12, 10:41    [11988575]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить