Как создать уникальный индекс по столбцу 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)

Есть идеи получше?

Источник

5 ответов

Решение

Уверен, что вы не можете сделать это, поскольку это нарушает цель уникальности.

Тем не менее, этот человек, кажется, имеет хорошую работу вокруг: http://sqlservercodebook.blogspot.com/2008/04/multiple-null-values-in-unique-index-in.html

Источник

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

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

Источник

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

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)
)
Источник

Можно использовать предикаты фильтра, чтобы указать, какие строки включать в индекс.

Из документации :

WHERE <filter_predicate> Создает отфильтрованный индекс, указав, какие строки включить в индекс. Отфильтрованный индекс должен быть некластеризованным индексом таблицы. Создает отфильтрованную статистику для строк данных в отфильтрованном индексе.

Пример:

      CREATE TABLE Table1 (
  NullableCol int NULL
)

CREATE UNIQUE INDEX IX_Table1 ON Table1 (NullableCol) WHERE NullableCol IS NOT NULL;
Источник

Строго говоря, уникальный обнуляемый столбец (или набор столбцов) может быть NULL (или записью из 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 со строкой в ​​the_entity, нам также необходимо добавить строку в the_relation.

Для строк в the_entity не было связано никаких уникальных значений (то есть для тех, которые мы поместили бы 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 не имеет уникального аналога, мы только делаем:

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" гарантирует, что все строки из the_entity появятся в результате, поместив NULL в столбец uniqnull, когда в the_relation нет соответствующих столбцов.

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

Источник
Другие вопросы по тегам