Создать уникальный индекс One/Only/Single NULL

Кажется, что в SQL Server уникальные индексы обрабатывают NULL как «просто еще одно значение», а не как в остальной части SQL, где сравнения с NULL возвращают NULL.

Скажем, у вас есть таблица (t) с уникальным индексом для столбца, допускающего значение NULL, K:

K     V
0     32
1     12
3     45

Все хорошо.

Но это также позволит

K     V
0     32
1     12
3     45
NULL  89     <-- Baaad

И наоборот, также позволит следующее:

K     V
NULL  89
0     32    <-- not good

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

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

Как я могу заставить SQL Server рассматривать значения NULL как значения NULL (и разрешать только одно значение NULL или любое количество уникальных значений в столбце) в уникальном индексе?


null sql sql-server unique-constraint
person Fowl    schedule 01.05.2013    source источник
comment
Я не понимаю, какое поведение вы на самом деле ищете. Вы используете фразу рассматривать NULL как NULL, как будто это должно быть очевидно, просто из фразы, но для меня это не так. Я также нахожу ваш второй пример неясным.   -  person Damien_The_Unbeliever    schedule 01.05.2013
comment
SQL-Server допускает не более одного NULL в столбце с уникальным ограничением. Другие СУБД (которые в данном случае соответствуют стандарту) допускают более одного значения Null. Вы можете обойти это, создав уникальный частичный индекс, но я не думаю, что это ваша цель. Не совсем понятно, какова ваша цель на самом деле.   -  person ypercubeᵀᴹ    schedule 01.05.2013
comment
На мой взгляд, любое сравнение с NULL должно давать NULL, поэтому, если столбец содержит только одно NULL, все сравнения с любым другим значением (включая NULL) должны завершиться неудачно. Я вижу, как может быть полезно быть «оптимистичным» и предполагать, что NULLs представляют неизвестные, которые уникальны, но в моем случае это было бы пессимистично и обеспечивало бы целостность базы данных.   -  person Fowl    schedule 01.05.2013
comment
Согласно стандарту, сравнение с NULL дает UNKNOWN, а не NULL. Это незначительная гнида, но если бы у нас был логический тип данных в SQL, мы ожидали бы, что сможем установить для столбца этого типа значение TRUE, FALSE, UNKNOWN или (если бы он допускал значение NULL) NULL. Только (насколько мне известно) MySQL объединяет NULL и UNKNOWN.   -  person Damien_The_Unbeliever    schedule 01.05.2013
comment
Я использую MS SQL, я ничего не знаю о стандартах :P Если серьезно, я думаю, что многие документы слишком упрощают обработку NULL до такой степени, что они оставляют вас с неполной ментальной моделью.   -  person Fowl    schedule 01.05.2013

Ответы (2)


arrow_upward
1
arrow_downward

Если интерпретация Andomar того, что вы хотите, верна, это может быть выполнимо, если у вас есть таблица, которая уже содержит все возможные значения K:

create table dbo.T (
    K int null,
    V int not null,
)
go
create table dbo.PossibleKs (
    K int not null
)
insert into dbo.PossibleKs (K) values (0),(1),(2)
go
create view dbo.TV
with schemabinding
as
    select pk.K
    from
        dbo.T t
            inner join
        dbo.PossibleKs pk
            on
                t.K = pk.K or
                t.K is null
GO
create unique clustered index IX_TV on dbo.TV (K)

И ваши тестовые случаи:

insert into dbo.T(K,V) values
(0,     32),
(1,     12),
(3,     45)
go
insert into dbo.T(K,V) values
(NULL,89)
--Msg 2601, Level 14, State 1, Line 1
--Cannot insert duplicate key row in object 'dbo.TV' with unique index 'IX_TV'. The duplicate key value is (0).
--The statement has been terminated.
go
delete from dbo.T
go
insert into dbo.T(K,V) values
(NULL,89)
go
insert into dbo.T(K,V) values
(0,     32)
--Msg 2601, Level 14, State 1, Line 1
--Cannot insert duplicate key row in object 'dbo.TV' with unique index 'IX_TV'. The duplicate key value is (0).
--The statement has been terminated.
person Damien_The_Unbeliever    schedule 01.05.2013

arrow_upward
0
arrow_downward

Итак, вам нужен либо один null, либо любое количество уникальных номеров. Я не думаю, что это можно надежно обеспечить с помощью ограничений.

Возможно, вы могли бы использовать триггер. Триггер должен будет отвечать на такие вопросы, как: вы обновляете строку до null? Есть ли уже строка null? Вы обновляете строку, которая уже была null? Этот триггер будет сложным и трудным в обслуживании.

Вы можете манипулировать таблицей с помощью хранимых процедур. Хранимые процедуры могут выполнять операции обновления/вставки/удаления в транзакции. Перед фиксацией они могут проверить, состоит ли таблица из одного null или любого количества других значений. Вы могли бы разумно утверждать это.

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

person Andomar    schedule 01.05.2013
comment
Да, триггер был бы неоптимальным. :) Я не думаю, что мой дизайн на самом деле такой уж необычный, он просто удаляет одну крошечную часть гибкости, которая значительно усложнила бы мои запросы для правильной обработки во всех случаях. Таким образом гарантируется, что этот тривиальный запрос работает правильно. - person Fowl; 01.05.2013