Optionale eindeutige Geschäftsschlüssel in SQL Server

Ein Konto kann angelegt werden, bevor ein externes System seine Kennung liefert. Viele Konten dürfen deshalb noch keine externe ID besitzen. Zwei Konten desselben Mandanten dürfen aber keine bekannte ID teilen. Diese Regel wird durch eine gewöhnliche nullable UNIQUE-Constraint nicht automatisch ausgedrückt.

Die teilnehmenden Zeilen bestimmen

Bei einem einzelnen nullable Schlüssel erlaubt ein gewöhnlicher eindeutiger SQL-Server-Index nur einen NULL-Eintrag. Bei zusammengesetzten Schlüsseln wird die gesamte Kombination geprüft. Das ist nicht gleichbedeutend mit dem Ignorieren aller Zeilen ohne optionale Kennung. Ein gefilterter eindeutiger Index beschreibt diese Teilnahme ausdrücklich.

Das Beispiel prüft ExternalId innerhalb von TenantId nur dann, wenn ExternalId nicht NULL ist.

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;

Die beiden fehlenden Kennungen in Mandant 10 sind erlaubt. ABC darf in Mandant 10 und 20 vorkommen, weil der Mandant zum Schlüssel gehört. Das zweite ABC innerhalb von Mandant 10 scheitert; anschließend stehen weiterhin vier Zeilen in der Tabelle. Der Filter wählt Teilnehmer, der Schlüssel definiert Kollisionen.

Verwenden Sie für Beziehungen weiterhin einen stabilen Primärschlüssel wie AccountId. Externe Kennungen können spät kommen, wechseln oder auslaufen. Kindtabellen an diesen Lebenszyklus zu binden erschwert das Datenmodell. Ein gefilterter eindeutiger Index ist außerdem kein allgemeines Fremdschlüsselziel für Zeilen außerhalb seines Filters.

Gleichheit fachlich festlegen

NULL, Leerstring und eine Folge von Leerzeichen sind unterschiedliche Eingaben, solange die Anwendung sie nicht bewusst vereinheitlicht. Bedeutet leer wirklich fehlend, normalisieren Sie vor dem Speichern und sichern Sie die erlaubte Darstellung ab. Sonst nimmt ein Leerstring als echter Wert am Index teil und kann kollidieren.

Zeichenkettenvergleiche folgen der Spaltencollation und den Vergleichsregeln von SQL Server. Großschreibung und Akzente können die Gleichheit beeinflussen. Auch nachgestellte Leerzeichen können bei gewöhnlichen Zeichenvergleichen als gleich gelten. Der Vertrag sollte zum externen System passen und nicht zufällig vom Datenbankstandard abhängen.

Unterscheidet der Lieferant Kennungen, die Ihre Collation gleichsetzt, dürfen Sie sie nicht still durch Kleinschreibung oder Abschneiden von Leerzeichen zusammenführen. Gelten mehrere Schreibweisen fachlich als gleich, muss die Normalisierung dagegen bei API, Import und Wartung identisch sein. Eine gespeicherte normalisierte Spalte kann die Regel sichtbar machen, sofern ihre Konsistenz abgesichert wird.

Vor dem Anlegen auf bestehenden Daten gruppieren Sie nichtleere Kandidaten nach dem vorgesehenen vollständigen Schlüssel. Verwenden Sie dabei die beabsichtigten Vergleichsregeln. Klären Sie, welches Konto Eigentümer einer Kennung ist und was mit abhängigen Daten geschieht. Beliebiges Löschen von Duplikaten ist keine technische Indexbereinigung.

Gleichzeitige Vergaben absichern

Eine vorherige SELECT-Prüfung erleichtert verständliche Meldungen, garantiert aber nichts bei Konkurrenz. Zwei Anfragen können beide keinen Treffer finden und anschließend schreiben. Der eindeutige Index entscheidet endgültig. Übersetzen Sie die relevante Duplicate-Key-Verletzung in einen verständlichen Konflikt.

Der Übergang von NULL zu einer bekannten Kennung unterliegt derselben Regel wie INSERT. Das gilt auch für die Änderung von TenantId. Testen Sie beide Übergänge, weil sie oft unterschiedliche Anwendungspfade nutzen. Prüfen Sie außerdem zwei Sitzungen, die gleichzeitig denselben Wert vergeben.

Sollen logisch gelöschte Konten ihre Kennung freigeben, muss die Teilnahmebedingung dies ausdrücken. Definieren Sie zugleich die Wiederherstellung: Ein altes Konto kann mit einem neuen Eigentümer kollidieren. Dürfen Kennungen niemals wiederverwendet werden, muss die Eindeutigkeit auch archivierte Konten umfassen.

Behandeln Sie den Index als Integritätsregel und als gepflegte Datenstruktur. Bestehende Duplikate verhindern seine Erstellung; auf großen Tabellen braucht die Migration Planung. Halten Sie erforderliche SET-Optionen für gefilterte Indizes konsistent. Eine kurze fachliche Beschreibung neben der Migration schützt die Regel vor späteren versehentlichen Änderungen.

Technische Referenzen: Microsoft Learn: Unique indexes · Microsoft Learn: Filtered indexes.