Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Ну всё! Упёрся в Unique Null...  [new]
DBTormentor1234567
Guest
Помогите, пожалуйста, кто знает!

Я хочу иметь в одной из таблиц (MS SQL Server 2000 + SP2) колонку с Unique-значениями, но чтобы уникальность не относилась к Null-значениям. Это никак не выходит!

Я вычитал в BOL:


"UNIQUE constraints can be:
...
Added to an existing table, provided that the column or combination of columns comprising the UNIQUE constraint contains only unique or NULL values. A table can contain multiple UNIQUE constraints.
...
When a UNIQUE constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ 2000 by default checks the existing data in the columns to ensure all values, except NULL, are unique. If a UNIQUE constraint is added to a column that has duplicated values, SQL Server returns an error and does not add the constraint.
...

Если NULLи уже есть, то MS SQL (Ent. Men.) говорит:

'titleauthor' table
- Unable to create index 'IX_titleauthor'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 6. Most significant primary key is '<NULL>'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.

А если пытаюсь вставить NULL в "ограниченное" поле, то MS SQL (Query Analyzer) говорит:

Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'IX_titleauthor'. Cannot insert duplicate key in object 'titleauthor'.
The statement has been terminated.

Заскриптованная "ограниченная" таблица выглядит следующим образом:

CREATE TABLE [titleauthor] (

[au_id] [varchar] (11) COLLATE Cyrillic_General_CI_AS NULL ,
[title_id] [varchar] (11) COLLATE Cyrillic_General_CI_AS NULL ,
[au_ord] [tinyint] NULL ,
[royaltyper] [int] NULL ,
CONSTRAINT [IX_titleauthor] UNIQUE NONCLUSTERED
(
[au_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO


Как же мне получить в одном поле и Unique и Null-значения одновременно?
11 июл 02, 21:38    [38638]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
Glory
Member

Откуда:
Сообщений: 104760
При UNIQUE constraints на одно поле в таблице может только ОДНА запись со значением NULL в этом поле.
Вторая запись со значением NULL будет нарушать уникальность и как и любое другое значение, которое уже имеется в таблице в данном поле.

Так что все логично и правильно.

По поводу первой цитаты, "...unique or NULL values" - переводится как "уникальные или NULL значения" и множественно число появляется из-за перечисления "уникальные или NULL", а не из-за того, что разрешены множественные "NULL значения"

По поводу второй цитаты, как ни странно у меня в BOL (правда эа исправленная версия) этот фрагмент выглядит так

"When a UNIQUE constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ 2000 by default checks the existing data in the columns to ensure all values are unique. If a UNIQUE constraint is added to a column that has duplicated values, SQL Server returns an error and does not add the constraint." Т.е. никаких заверений насчет "except NULL" нет.


По поводу - что делать.
IMHO надо пересматривать логику приложения. Что это за ограничение на уникальность, если в базе напрмер 10000 записей и у всех в поле NULL ? Кому нужна такая проверка ?
Стандартными constraint-ами вы ничего не добъетесь. Попрбуйте через триггер INSTEAD OF
11 июл 02, 22:16    [38643]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
Сергей Тихонов
Member

Откуда: Киев
Сообщений: 787
Это и не выйдет...
Можно повесить констрейнт на таблицу с данными, указав WITH NOCHECK. Но все равно потом при вставки данных оно будет ругаться, если это будет повторное NULL-значение.
Мне кажется, Вы что-то перемудрили там у себя...
11 июл 02, 22:17    [38645]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31214
А как-же эта фраза из BOL:
When a UNIQUE constraint is added to an existing column or columns in the table, Microsoft® SQL Server™ 2000 by default checks the existing data in the columns to ensure all values, except NULL, are unique.
По-моему, такая возможность очень часто нужна, жаль что этого нельзя делать.
12 июл 02, 13:00    [38734]     Ответить | Цитировать Сообщить модератору
 Спасибо всем за ответы и комментарии. Итак...  [new]
DBTormentor
Member

Откуда:
Сообщений: 2
To Glory:
1.
Остается предположить, что у Ваша версия BOL просто соответствует действительности! - MS SQL Server 2000.
2.
>>> "Что это за ограничение на уникальность, если в базе напрмер 10000 записей и у всех в поле NULL ? Кому нужна такая проверка ?"
Ну как же? Логика простая: по какой-то причине нам пока неизвестно значение этого поля, а когда будет известно - то другого такого значения быть не должно (номер ИНН, пенсионного свидетельства и т.п.).
3.
Действительно, остается только триггер. Прямо как здесь.

To Сергей Тихонов:
"WITH NOCHECK" относится только к Foreign Key и Check, но не к Unique или Primary Key Constraints. Да и потом, не отключать же каждый раз ограничение для вставки Null-значения.

To alexeyvg:
Да, абсолютно согласен!
12 июл 02, 15:44    [38785]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
DBTormentor
Member

Откуда:
Сообщений: 2
Кому интересно - нашёл заметку о теме на сайте Microsoft: http://www.microsoft.com/sql/techinfo/tips/development/ensuringnonnull.asp
(с вариантом триггера!)
19 июл 02, 11:08    [40048]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
Miha
Guest
Где-то видел оригинальное решение:
создать indexed view с уникальным констраинтом по данному полю и отсечь NULL значения.
правда работать будет только в SQL2000 Enterprise
21 июл 02, 11:09    [40298]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
Dmitrius
Guest
GO


CREATE FUNCTION fn_CheckClient (@client_ID dom_ID, @login dom_Login)
RETURNS dom_Flag
AS
BEGIN
IF (EXISTS(SELECT * FROM Clients
WHERE login = @login
AND (@client_ID IS NULL OR client_ID <> @client_ID)))
RETURN 1
RETURN 0
END

GO




CREATE TABLE Clients (
client_ID dom_ID IDENTITY,
city_ID int NOT NULL,
name dom_Name,
address dom_Address NULL,
phone dom_Phone,
fax dom_Phone,
inn dom_INN,
okonh dom_OKONH NULL,
okpo dom_OKPO,
bank_number dom_Bank_Number,
corr_number dom_Corr_Number,
description dom_Description,
code dom_ID,
login dom_Login,
password dom_Password,
CONSTRAINT R_6
FOREIGN KEY (city_ID)
REFERENCES Cities,
CONSTRAINT chk_Login6
CHECK (login IS NULL OR dbo.fn_CheckClient (client_ID, login) = 0),
CONSTRAINT chk_Password6
CHECK ((login IS NULL AND password IS NULL) OR (login IS NOT NULL AND password IS NOT NULL)),
CONSTRAINT XAK1Clients
UNIQUE (
name
)
)
go

CREATE INDEX XIE1Clients ON Clients
(
code
)
go


ALTER TABLE Clients
ADD CONSTRAINT XPKClients PRIMARY KEY (client_ID)
go

ALTER TABLE Clients
NOCHECK CONSTRAINT R_6


GO
11 сен 02, 16:24    [53636]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
Hibernate
Member

Откуда: Киев
Сообщений: 1670
2Glory (это ни в коем случае не наезд, просто высказывая возражения Вашим мыслям легче выразить свои :-)

Далее я имею ввиду логику вообще, а не логику MSSQL, что на мой взгляд есть не одно и то-же ;-)

Вторая запись со значением NULL будет нарушать уникальность и как и любое другое значение, которое уже имеется в таблице в данном поле.
Так что все логично и правильно.


по поводу логичности (даже в пределах логики MSSQL) трудно согласиться - разве Null=Null ? нет не равно. Просто неизвестно.

Что это за ограничение на уникальность, если в базе напрмер 10000 записей и у всех в поле NULL ? Кому нужна такая проверка ?

Простейшая ситуация - атрибут, значение которого должно быть уникальным. Но оно может быть неизвестным, тоесть иметь значение Null, вернее не иметь значения вообще.

Я думаю, основная проблема в том, что я, да и многие трактуют NULL как отсутствие какого-либо значения, а MSSQL трактует его как совершенно определенное значение, правда неизвестное... Наверное, и то и другое имеет какой-то смысл....

P.S. Я понимаю, что глупо "гавкать на караван, который идет" - но просто вот эта ситуация с NULL в UNIQUE constraint в моей практике в половине случаев заставляет отказываться от UNIQUE constraint и реализовывать это по-своему ручками, что уже просто достало...
12 сен 02, 13:23    [53986]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
Glory
Member

Откуда:
Сообщений: 104760
Я думаю, основная проблема в том, что я, да и многие трактуют NULL как отсутствие какого-либо значения, а MSSQL трактует его как совершенно определенное значение, правда неизвестное

Просто нечего добавить к этому - именно две записи, в которых столбец имеет значение "неизвестно"(NULL), и нарушают уникальность сточки зрения MSSQL.

IMHO проблему можно решать путем выноса необязательных к заполнению атрибутов в отдельную дочернюю таблицу.
12 сен 02, 19:38    [54256]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Ну всё! Упёрся в Unique Null...  [new]
Nikolay****
Member

Откуда:
Сообщений: 223
Ни в Oracle, ни в Postgres такой ерунды не было.....
Вот на днях столкнулся.....долго не доумевал....теперь понял!!!!
16 ноя 09, 22:00    [7936562]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
aleks2
Guest
Если все упирается в такую ерунду...
https://www.sql.ru/forum/actualthread.aspx?tid=352578
17 ноя 09, 08:29    [7937116]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
V. Goncharenko
Member

Откуда:
Сообщений: 126
Nikolay****
Ни в Oracle, ни в Postgres такой ерунды не было.....
Вот на днях столкнулся.....долго не доумевал....теперь понял!!!!

На 2008 могли бы вывернуться с использованием filtered indexes
use tempdb;

if OBJECT_ID('tempdb..#t1') is not null drop table t1;

create table t1([id] int not null identity, [refid] int null);

create unique index ix_t1_refid on t1([refid]) where [refid] is not null;

insert into t1([refid]) values (1);
insert into t1([refid]) values (2);
insert into t1([refid]) values (null);
-- барабанная дробь...
insert into t1([refid]) values (null);    

drop table t1;
17 ноя 09, 08:37    [7937123]     Ответить | Цитировать Сообщить модератору
 Re: Ну всё! Упёрся в Unique Null...  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
aleks2, V. Goncharenko,

а как вы поняли, что Nikolay**** понял?
Не понял!
17 ноя 09, 08:57    [7937166]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить