Необязательные уникальные бизнес-ключи в SQL Server

Учетная запись может появиться до того, как внешняя система назначит ей идентификатор. Поэтому у многих записей он отсутствует, но две записи одного арендатора не должны разделять известное значение. Обычное ограничение UNIQUE для nullable-столбца не выражает эту договоренность автоматически.

Определить участников проверки

Для одного nullable-столбца обычный уникальный индекс SQL Server допускает одну запись NULL. В составном ключе проверяется вся комбинация. Это не правило игнорировать любые строки без необязательного поля. Уникальный фильтрованный индекс позволяет задать участие явно.

Пример обеспечивает уникальность ExternalId внутри TenantId только для строк с заполненным внешним идентификатором.

CREATE TABLE #Accounts
(
    AccountId int NOT NULL PRIMARY KEY,
    TenantId int NOT NULL,
    ExternalId nvarchar(100) NULL
);
CREATE UNIQUE INDEX UX_Accounts_External
ON #Accounts (TenantId, ExternalId)
WHERE ExternalId IS NOT NULL;

INSERT #Accounts VALUES
(1, 10, NULL), (2, 10, NULL),
(3, 10, N'ABC'), (4, 20, N'ABC');

BEGIN TRY
    INSERT #Accounts VALUES (5, 10, N'ABC');
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS DuplicateError;
END CATCH;

SELECT AccountId, TenantId, ExternalId
FROM #Accounts ORDER BY AccountId;
DROP TABLE #Accounts;

Оба отсутствующих значения арендатора 10 разрешены. ABC может встречаться у арендаторов 10 и 20, поскольку арендатор входит в ключ. Второй ABC у арендатора 10 отклоняется, и остаются четыре строки. Фильтр выбирает участников, столбцы ключа определяют совпадение.

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

Задать смысл равенства

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

Равенство зависит от collation столбца и правил сравнения SQL Server. Чувствительность к регистру и акцентам меняет набор совпадений. Конечные пробелы также могут считаться равными при обычном сравнении строк. Контракт должен соответствовать внешней системе, а не случайному умолчанию базы.

Если поставщик различает значения, которые ваша collation считает одинаковыми, не объединяйте их молча переводом в нижний регистр или удалением пробелов. Если бизнес действительно считает несколько форм эквивалентными, нормализация должна совпадать в API, импорте и скриптах. Хранимый нормализованный столбец может сделать правило видимым при гарантии его согласованности.

До создания индекса на существующих данных сгруппируйте значения не NULL по полному планируемому ключу. Используйте именно нужную семантику сравнения. Разберите дубликаты и решите, какая запись владеет идентификатором и что происходит с зависимостями. Удаление случайной строки является бизнес-решением, а не обслуживанием индекса.

Обеспечить одновременное назначение

Предварительный SELECT полезен для удобного сообщения, но не гарантирует уникальность. Два запроса могут одновременно не найти значение и затем попытаться вставить его. Окончательное решение принимает уникальный индекс. Преобразуйте соответствующую ошибку дубликата в понятный конфликт приложения.

Переход от NULL к известному значению должен соблюдать ту же договоренность, что и INSERT. Смена TenantId тоже может вызвать конфликт. Протестируйте оба перехода, особенно если они выполняются разными экранами. Добавьте тест двух сессий, одновременно назначающих одинаковое значение.

Если логически удаленные записи освобождают идентификатор, выразите это условием участия. Одновременно определите восстановление: старая запись может конфликтовать с новым владельцем. При запрете повторного использования уникальность должна охватывать и архив.

Наконец, рассматривайте индекс как правило целостности и обслуживаемую структуру. Дубликаты мешают его созданию, а на большой таблице миграция требует планирования. Поддерживайте нужные SET-настройки фильтрованных индексов. Запишите бизнес-правило рядом с миграцией, включая примеры допустимых отсутствующих значений и запрещенных повторов. Такая документация помогает не заменить фильтр обычным UNIQUE при следующем рефакторинге и не изменить поведение незаметно для интеграции.

Техническая документация: Microsoft Learn: Unique indexes · Microsoft Learn: Filtered indexes.