Как создать уникальный индекс в столбце NULL?

Я использую SQL Server 2005. Я хочу, чтобы значения в столбце были уникальными, позволяя NULLS.

Мое текущее решение включает в себя уникальный индекс для такого вида:

CREATE VIEW vw_unq WITH SCHEMABINDING AS
    SELECT Column1
      FROM MyTable
     WHERE Column1 IS NOT NULL

CREATE UNIQUE CLUSTERED INDEX unq_idx ON vw_unq (Column1)

Любые лучшие идеи?

Ответы

Ответ 2

Используя SQL Server 2008, вы можете создать отфильтрованный индекс: http://msdn.microsoft.com/en-us/library/cc280372.aspx. (Я вижу, что Саймон добавил это как комментарий, но считал, что заслужил его собственный ответ, поскольку комментарий легко пропущен)

Другая опция - это триггер для проверки уникальности, но это может повлиять на производительность.

Ответ 3

Вычисленный трюк столбца широко известен как "нульбастер"; мои заметки кредитуют Стив Касс:

CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X  int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)

Ответ 4

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

Однако это не означает, что понятие "уникальные столбцы с нулевым значением" действительно; чтобы фактически реализовать его в любой реляционной базе данных, нам просто нужно иметь в виду, что такие базы данных должны быть нормализованы для правильной работы, а нормализация обычно включает в себя добавление нескольких (не-сущностей) дополнительных таблиц для установления отношений между объектами.

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

Предположим, что информация, представленная таблицей, такова:

create table the_entity_incorrect
(
  id integer,
  uniqnull integer null, /* we want this to be "unique and nullable" */
  primary key (id)
);

Мы можем сделать это, разделив uniqnull отдельно и добавив вторую таблицу, чтобы установить связь между значениями uniqnull и the_entity (а не иметь uniqnull) внутри "the_entity" ):

create table the_entity
(
  id integer,
  primary key(id)
);

create table the_relation
(
  the_entity_id integer not null,
  uniqnull integer not null,

  unique(the_entity_id),
  unique(uniqnull),
  /* primary key can be both or either of the_entity_id or uniqnull */
  primary key (the_entity_id, uniqnull), 
  foreign key (the_entity_id) references the_entity(id)
);

Чтобы связать значение uniqnull с строкой в__определении, нам нужно также добавить строку в ссылку.

Для строк в объекте не были сопоставлены значения uniqnull (т.е. для тех, которые мы бы поместили в NULL в in_entity_incorrect), мы просто не добавляем строку в ссылку.

Обратите внимание, что значения для uniqnull будут уникальными для всех параметров, а также заметят, что для каждого значения в__определении в переменной может быть не более одного значения, так как первичный и внешний ключи на ней обеспечивают это.

Затем, если значение 5 для uniqnull должно быть связано с id_entity из 3, нам необходимо:

start transaction;
insert into the_entity (id) values (3); 
insert into the_relation (the_entity_id, uniqnull) values (3, 5);
commit;

И, если значение id, равное 10 для the_entity, не имеет uniqnull-аналога, мы делаем только:

start transaction;
insert into the_entity (id) values (10); 
commit;

Чтобы денормализовать эту информацию и получить данные, таблица, подобная функции the_entity_incorrect, должна быть:

select
  id, uniqnull
from
  the_entity left outer join the_relation
on
  the_entity.id = the_relation.the_entity_id
;

Оператор "left external join" гарантирует, что все строки from_entity появятся в результате, поместив NULL в столбец uniqnull, если в этой_реляции нет соответствующих столбцов.

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