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

Я использую SQL Server 2005

У меня есть поле, которое должно содержать уникальное значение или значение NULL. Я думаю, что я должен обеспечить это либо CHECK CONSTRAINT или TRIGGER for INSERT, UPDATE,

Есть ли преимущество в использовании здесь ограничения по сравнению с триггером (или наоборот)? Как может выглядеть такое ограничение / триггер?

Или есть другой, более подходящий вариант, который я не рассматривал?

Источник

7 ответов

Решение

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

Ограничение:

   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

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

Источник

Я создаю представление с индексом, который игнорирует нули через предложение 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, вам нужно будет " УСТАНОВИТЬ ARITHABORT ON" непосредственно перед выполнением любой вставки, обновления или удаления таблицы. Уверен, что это не требуется на MSSQL 2005 и выше.

Источник

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

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)

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

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

В следующем примере все еще существует вероятность коллизий между столбцом 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
Источник

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

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

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

Источник

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

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

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

Источник

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

Источник

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

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

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

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