Antwort.

Hintergrund der Frage

Die Kontrolle der Eindeutigkeit von Geschäftsschlüsseln (nicht nur nach ID, sondern auch nach ‚natürlichen‘ Feldern) ist seit langem ein Thema in Unternehmensanwendungen. Oft sind solche Schlüssel komplex (mehrere Spalten) und erlauben NULL-Werte. Die standardmäßigen SQL-Mittel – UNIQUE CONSTRAINT oder eindeutige Indizes – haben Einschränkungen: Nach dem SQL-Standard gelten mehrere Zeilen, bei denen mindestens einer der Komponenten des UNIQUE-Schlüssels NULL ist, als einzigartig und verletzen somit nicht die Einschränkung.

Problem

Die Geschäftslogik kann verlangen, dass die Menge der Spalten (einschließlich der NULL zulassenden) insgesamt eindeutig ist, während das Verhalten des SQL-Standards (NULL ≠ NULL) dieses Szenario bricht. Zum Beispiel haben wir eine Tabelle mit dem Paar Spalten (passport_number, passport_series), und mindestens einer von ihnen kann NULL sein, aber wenn die Werte übereinstimmen, müssen wir Duplikate verbieten.

Lösung

In den meisten verbreiteten DBMS ist die Lösung, die Eindeutigkeitsprüfung über Trigger zu implementieren oder partielle Indizes mit einer Bedingung zu verwenden, oder Funktionen zu nutzen, die NULLs als gleiche Werte vergleichen (zum Beispiel ISNULL oder COALESCE). Hier ist ein Beispiel in PostgreSQL mit der Verwendung eines einzigartigen Indexes auf Ausdruck:

CREATE UNIQUE INDEX idx_passport_unique
ON persons (
  COALESCE(passport_number, ''),
  COALESCE(passport_series, '')
);

Auf der Ebene der Anwendungslogik muss oft eine zusätzliche Überprüfung dupliziert werden, um Duplikate beim Einfügen nicht zu übersehen.

Wichtige Merkmale:

Der standardmäßige UNIQUE CONSTRAINT funktioniert nicht, wenn mindestens ein NULL im Schlüssel beteiligt ist.
Die Lösung hängt vom DBMS ab: partielle Indizes, berechnete Spalten, Trigger.
Die Eindeutigkeitsprüfung kann auf die Geschäftslogik übertragen werden, wenn es nicht möglich ist, einen korrekten Index zu erstellen.

Fangfragen.

Kann man sicher sein, dass ein eindeutiger Index das Einfügen von Duplikaten verhindert, wenn unter den Spalten nullable-Spalten sind?

Nein, das kann man nicht. In SQL hat der Umgang mit NULL eine besondere Eigenheit: Zeilen mit denselben Werten, wobei mindestens einer NULL ist, werden von dem Index als unterschiedlich betrachtet und die gleichzeitige Speicherung ist erlaubt.

Können verschiedene DBMS NULL-Eindeutigkeit unterschiedlich umsetzen?

Ja, es gibt Unterschiede zwischen ihnen. Zum Beispiel erlaubt ein eindeutiger Index in Oracle mehrere Zeilen mit NULL, während MS SQL nur eine zulässt. PostgreSQL kann partielle Indizes über Ausdrücke erstellen.

Ist es möglich, das Problem nur auf DDL-Ebene und ohne Trigger zu umgehen?

In einigen DBMS – ja, über Ausdrücke, wie COALESCE. Aber nicht immer, und wenn ein komplexes Verhalten erforderlich ist (NULL sollte als Duplikat betrachtet werden), müssen logische Prüfungen oder Trigger verwendet werden.

Typische Fehler und Anti-Pattern

Sich auf den Standard-UNIQUE verlassen, wenn NULL in den Spalten zulässig ist.
Duplikate auf der Ebene der Geschäftslogik nicht überprüfen.
Trigger ohne Berücksichtigung der Leistung erstellen.

Beispiel aus der Praxis

Negativer Fall

In einer nicht kritischen Datenbank wurde beschlossen, sich auf einen eindeutigen Index über (E-Mail, Abteilungscode) zu verlassen, wobei beide Felder NULL zulassen. Infolgedessen traten mehrfach Duplikate auf, was externe Integrationen störte.

Vorteile:

Einfaches DDL

Nachteile:

Unauffällige Duplikate, inkonsistente Daten, Fehler in Berichten

Positiver Fall

In PostgreSQL wurde der Geschäftsschlüssel mit den Feldern (Reisepassnummer, Serie) über einen Index mit COALESCE implementiert und zusätzlich eine Überprüfung auf der Anwendungsebene hinzugefügt.