Значение поля должно быть уникальным, если оно не равно NULL

Ответ 1

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

Constraint:

   field is null or dbo.fn_count_maximum_of_field(field) < 2

РЕДАКТИРОВАТЬ Я сейчас не могу вспомнить - и не могу это проверить - проверяется ли проверка ограничений перед вставкой/обновлением или после. Я думаю, что после того, как вставка/обновление вернется к отказу. Если окажется, что я ошибаюсь, то 2 выше должно быть 1.

Функция таблицы возвращает int и использует следующий выбор для ее получения

   declare @retVal int

   select @retVal = max(occurrences)
   from ( 
        select field, count(*) as occurrences
        from dbo.tbl
        where field = @field
        group by field
   ) tmp

Это должно быть достаточно быстро, если ваш столбец как (не уникальный) индекс на нем.

Ответ 2

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

create view dbo.UniqueAssetTag with schemabinding
as
select asset_tag
from dbo.equipment
where asset_tag is not null

GO

create unique clustered index ix_UniqueAssetTag
on UniqueAssetTag(asset_tag)

GO

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

Примечание: Если вы используете mssql 2000, вам нужно будет " SET ARITHABORT ON" прямо перед тем, как любая вставка, обновление или удаление будет выполнена в таблице. Довольно точно, что это не требуется на mssql 2005 и выше.

Ответ 3

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

ALTER TABLE MYTABLE 
ADD COL2 AS (CASE WHEN COL1 IS NULL THEN CAST(ID AS NVARCHAR(255)) ELSE COL1 END)

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2)   

Предполагается, что идентификатор - это PK вашей таблицы, а COL1 - столбец "уникальный или нулевой".

Вычисленный столбец (COL2) будет использовать значение PK, если ваш уникальный столбец имеет значение null.

По-прежнему существует вероятность столкновений между столбцом ID и COL1 в следующем примере:

ID     COL1    COL2
1     [NULL]    1
2        1      1

Чтобы обойти это, я обычно создаю другой вычисленный столбец, в котором хранится ли значение в COL2 из столбца ID или столбца COL1:

 ALTER TABLE MYTABLE 
 ADD COL3 AS (CASE WHEN COL1 IS NULL THEN 1 ELSE 0 END)

Индекс должен быть изменен на:

CREATE UNIQUE INDEX UQ_COL2 ON MYTABLE (COL2, COL3)   

Теперь индекс находится на обоих вычисленных столбцах COL2 и COL3, поэтому нет проблемы:

ID     COL1    COL2   COL3
1     [NULL]    1       1
2        1      1       0

Ответ 4

В Oracle уникальный ключ позволит несколько NULL.

В SQL Server 2005 хороший подход - сделать ваши вставки через представление и отключить прямые вставки в таблицу.

Вот пример кода.

Ответ 5

Есть ли первичный ключ в этой таблице, может быть, столбец Identity? Вы можете создать уникальный ключ, который является составной частью поля, в котором вы выполняете уникальность в сочетании с первичным ключом.

Здесь обсуждается только такая проблема: http://blog.sqlauthority.com/2008/09/07/sql-server-explanation-about-usage-of-unique-index-and-unique-constraint/

FYI - SQL Server 2008 вводит отфильтрованные индексы, которые позволят вам подойти к этому немного по-другому.

Ответ 6

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

Ответ 7

Ограничение намного легче, чем триггер, хотя уникальное ограничение является фактически индексом.

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

Это было запрошено у MS, чтобы игнорировать NULLS, но SQL 2008 отфильтровал индексы (как упоминалось, когда я набираю это)