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

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

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

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

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)
)

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

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

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

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 со строкой в the_entity, нам нужно также добавить строку в the_relation.

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

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

тогда, если значение 5 для uniqnull должно быть связано с идентификатором the_entity 3, нам нужно:

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

и, если значение идентификатора 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
;

" левый внешний оператор join " гарантирует, что все строки из the_entity появятся в результате, помещая NULL в столбец uniqnull, когда в the_relation нет совпадающих столбцов.

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