Chaves de negócio opcionais e únicas no SQL Server

Uma conta pode existir antes de receber seu identificador externo. Várias contas podem não ter essa informação, mas duas do mesmo tenant não devem compartilhar um valor conhecido. Uma constraint UNIQUE nullable comum não expressa automaticamente essa regra.

Definir quais linhas participam

Em uma única coluna nullable, um índice único comum do SQL Server permite uma entrada NULL. Em uma chave composta, a unicidade considera a combinação completa. Isso não significa ignorar todas as linhas sem o campo opcional. Um índice único filtrado permite declarar essa participação.

O exemplo impõe unicidade de ExternalId dentro de TenantId somente quando o identificador externo não é NULL.

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;

As duas ausências no tenant 10 são aceitas. ABC pode existir nos tenants 10 e 20 porque o tenant faz parte da chave. O segundo ABC no tenant 10 falha e restam quatro linhas. O filtro escolhe os participantes; a chave determina as colisões.

Mantenha uma chave primária estável como AccountId para relacionamentos. Um identificador externo pode chegar depois, mudar ou ser aposentado. Fazer tabelas dependentes acompanharem esse ciclo complica o modelo. Um índice único filtrado também não oferece um destino geral de chave estrangeira para linhas fora do filtro.

Especificar o que significa igualdade

NULL, string vazia e string de espaços não são equivalentes sem uma decisão da aplicação. Se vazio significa ausente, normalize explicitamente antes de armazenar e imponha a representação aceita. Caso contrário, uma string vazia participa como valor real e pode gerar colisão.

A igualdade segue a collation da coluna e as regras de comparação do SQL Server. Sensibilidade a maiúsculas e acentos pode mudar quais identificadores são iguais. Espaços finais também podem ser considerados iguais em comparações comuns. O contrato deve seguir o sistema externo, não um padrão acidental da base.

Se o fornecedor distingue valores que sua collation considera iguais, não os una silenciosamente com minúsculas ou remoção de espaços. Se o negócio considera várias representações equivalentes, normalize de modo consistente em API, importações e scripts. Uma coluna normalizada armazenada pode explicitar a regra, desde que sua consistência seja garantida.

Antes de criar o índice em dados existentes, agrupe valores não NULL pela chave completa pretendida e examine duplicatas. Use a semântica de comparação desejada. Decida qual conta mantém o identificador e o destino dos registros dependentes. Excluir duplicatas arbitrariamente é uma decisão de negócio.

Deixar o banco arbitrar concorrência

Um SELECT anterior pode produzir uma mensagem amigável, mas não garante unicidade. Duas requisições podem constatar ausência antes de qualquer uma inserir. O índice é o árbitro final. Capture a violação relevante de chave duplicada e apresente um conflito compreensível.

A mudança de NULL para valor conhecido precisa seguir a mesma regra do INSERT. Alterar TenantId também. Teste essas transições, pois podem seguir caminhos diferentes na aplicação. Inclua duas sessões atribuindo o mesmo valor simultaneamente, além de duplicatas sequenciais.

Se contas excluídas logicamente devem liberar o identificador, acrescente essa política à participação no índice. Defina também a restauração: uma conta antiga pode conflitar com um novo proprietário. Se o identificador nunca deve ser reutilizado, mantenha a unicidade sobre contas arquivadas.

Por fim, trate o índice como regra de integridade e estrutura mantida. Sua criação pode falhar por duplicatas existentes e precisa de planejamento em uma tabela grande. Mantenha consistentes as opções SET exigidas por índices filtrados. Documente a regra de negócio junto da migração para que alterações futuras preservem a fronteira pretendida.

Referências técnicas: Microsoft Learn: Unique indexes · Microsoft Learn: Filtered indexes.