Concevoir des clés métier uniques mais facultatives

Un compte peut exister avant de recevoir son identifiant externe. Plusieurs comptes peuvent donc ne pas en avoir, tandis que deux comptes du même tenant ne doivent pas partager une valeur connue. Une contrainte UNIQUE nullable ordinaire ne traduit pas automatiquement cette règle.

Définir les lignes soumises à la règle

Sur une seule colonne nullable, un index unique ordinaire SQL Server autorise une seule entrée NULL. Avec plusieurs colonnes, l'unicité porte sur la combinaison complète. Ce n'est pas une instruction pour ignorer toutes les lignes dont un champ facultatif manque. Un index unique filtré exprime cette sélection.

L'exemple impose l'unicité de ExternalId dans TenantId uniquement lorsque l'identifiant externe est renseigné.

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;

Les deux identifiants absents du tenant 10 sont acceptés. ABC peut exister dans les tenants 10 et 20, car le tenant fait partie de la clé. Le deuxième ABC du tenant 10 échoue et quatre lignes restent présentes. Le filtre choisit les participants ; la clé définit les collisions entre eux.

Conservez une clé primaire stable comme AccountId pour les relations. Une référence externe peut arriver tard, changer ou être retirée. Lier les tables enfants à ce cycle complique souvent le modèle. Un index unique filtré ne fournit pas non plus une cible générale de clé étrangère pour les lignes hors filtre.

Choisir le sens de l'égalité

NULL, chaîne vide et chaîne d'espaces ne représentent pas automatiquement le même état. Si une entrée vide signifie absence, normalisez-la volontairement avant stockage et contrôlez la représentation autorisée. Sinon, la chaîne vide participe à l'index comme une vraie valeur.

L'égalité dépend de la collation de la colonne et des règles de comparaison SQL Server. Sensibilité à la casse et aux accents peuvent changer les collisions. Des espaces finaux peuvent aussi être considérés égaux dans les comparaisons ordinaires. Le contrat doit suivre le système externe, pas un défaut historique de la base.

Si le fournisseur distingue des identifiants que votre collation rapproche, ne les fusionnez pas silencieusement en minuscules ou en supprimant des espaces. À l'inverse, si plusieurs formes sont équivalentes pour le métier, normalisez-les de la même manière dans API, imports et scripts. Une colonne normalisée stockée peut rendre cette règle visible si sa cohérence est garantie.

Avant de créer l'index sur des données existantes, regroupez les valeurs non NULL par la clé exacte prévue et examinez les doublons. Utilisez les comparaisons voulues. Déterminez le compte propriétaire et le devenir des dépendances. Supprimer arbitrairement des comptes n'est pas une simple opération d'entretien.

Arbitrer les affectations concurrentes

Un SELECT préalable peut aider à produire un message convivial, mais ne garantit pas l'unicité. Deux demandes peuvent toutes deux constater l'absence avant leur insertion. L'index reste l'arbitre final. Traduisez la violation pertinente de clé dupliquée en conflit compréhensible.

Passer de NULL à une valeur connue doit respecter la même règle qu'une insertion. Changer TenantId également. Testez ces transitions, souvent exposées par des chemins applicatifs différents. Testez aussi deux sessions attribuant simultanément la même valeur.

Si les comptes supprimés logiquement doivent libérer leur identifiant, incluez cette politique dans les lignes participantes. Définissez alors la restauration : un ancien compte peut entrer en conflit avec un nouveau propriétaire. Si la réutilisation est interdite, l'unicité doit au contraire couvrir les comptes archivés.

Enfin, l'index est une règle d'intégrité et une structure entretenue. Sa création échoue sur des doublons existants et mérite une migration planifiée sur une grande table. Maintenez les options SET requises pour les index filtrés. Une description métier près de la migration aidera les changements futurs à conserver la bonne frontière.

Références techniques: Microsoft Learn: Unique indexes · Microsoft Learn: Filtered indexes.