Antwort.

In relationalen Datenbanken liegt die Aufgabe, die Einzigartigkeit zu gewährleisten, häufig bei UNIQUE-Einschränkungen. In der Geschäftspraxis gibt es jedoch Situationen, in denen die Einzigartigkeit anhand einer Kombination von Feldern erforderlich ist, von denen einige NULL sein können (z. B. eine einzigartige Kombination aus E-Mail und Telefon, wobei das Telefon unbekannt sein kann).

Hintergrund: Standardmäßige UNIQUE CONSTRAINTs in SQL garantieren keine Einzigartigkeit, wenn eine der Spalten NULL ist – die Spezifikation betrachtet solche Werte als wechselseitig ausschließend.

Problem: Für komplexe Schlüssel mit NULL führen die standardmäßigen Einschränkungen zu Duplikaten. Dies ist besonders kritisch, wenn die Datenintegrität notwendig ist: bei Importen, Migrationen, Massenaktualisierungen.

Lösung: Verwenden Sie eine berechnete Spalte, die alle Werte (einschließlich NULL) berücksichtigt, und legen Sie die Einzigartigkeit auf diese Spalte fest oder verwenden Sie Trigger-Logik.

Beispielcode:

ALTER TABLE my_table
ADD computed_uniqueness AS (
   ISNULL(email, '') + '#' + ISNULL(phone, '')
);   
CREATE UNIQUE INDEX idx_my_table_computed_uniqueness ON my_table(computed_uniqueness);

Oder (für PostgreSQL, wo Ausdrücke im Index und NULL als unterschiedlich betrachtet werden):

CREATE UNIQUE INDEX idx_unique_email_phone ON my_table ((COALESCE(email, '##')),(COALESCE(phone, '##')));

Wichtige Merkmale:

Eindeutige Verwaltung der Einzigartigkeit von Kombinationen mit NULL.
Möglichkeit zur Automatisierung und Unterstützung auf DB-Ebene.
Unterstützung selbst für komplexe und sich ändernde Schemata.

Tricksereien.

Kann man einen normalen UNIQUE-Index verwenden, um die Einzigartigkeit zu gewährleisten, wenn einige Spalten NULL zulassen?

Nein. Laut ANSI SQL erlaubt ein UNIQUE-Index mehrere Zeilen, bei denen mindestens eine der Spalten in der Kombination NULL ist, da NULL nicht als gleich zu einem anderen Wert, einschließlich NULL, betrachtet wird.

Was ist der Unterschied zwischen der Verwendung eines eindeutigen Index auf einem Ausdruck und der Verwendung eines BEFORE INSERT-Triggers zur Überprüfung der Einzigartigkeit?

Ein eindeutiger Index ist einfacher zu warten und schneller in der Ausführung, kann jedoch nicht immer komplexe Geschäftsregeln (z. B. Ausnahmen oder benutzerdefinierte Kombinationen) umsetzen. Trigger sind flexibler, langsamer und schwieriger zu warten.

Beispiel:

CREATE OR REPLACE FUNCTION check_custom_unique()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM my_table
    WHERE COALESCE(NEW.email, '##') = COALESCE(email, '##')
      AND COALESCE(NEW.phone, '##') = COALESCE(phone, '##')
  ) THEN
    RAISE EXCEPTION 'Duplikat gefunden';
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_custom_unique
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION check_custom_unique();

Kann man DISTINCT in SELECT verwenden, um auf Anwendungsebene die Einzigartigkeit zu gewährleisten?

Ja, aber nur für die Auswahl - es verhindert nicht die Einführung von Duplikaten bei Datenänderungen und ist kein Ersatz für Einschränkungen auf Tabellenebene.

Typische Fehler und Anti-Patterns

Versuche, UNIQUE CONSTRAINT zu verwenden, wo der Schlüssel eine Kombination mit möglichen NULL ist.
Überprüfung der Einzigartigkeit nur auf Anwendungsebene (um die DB herum).
Übermäßige Komplexität von Triggern ohne explizite Indizes - Verlust der Leistung.

Beispiele aus dem Leben

Negativer Fall

Ein Unternehmen implementiert die Einzigartigkeit von E-Mail + Telefon über UNIQUE(email, phone). In der Datenbank entstehen Duplikate bei E-Mail, wenn Telefon=NULL.

Vorteile:

Einfachheit der Implementierung.

Nachteile:

Verlust der Integrität, Duplikate.
Unbemerkte Fehler, die in der Analyse schwer nachzuvollziehen sind.

Positiver Fall

Es wird eine berechnete Spalte verwendet (COALESCE(email, '##') + '#' + COALESCE(phone, '')), auf die ein eindeutiger Index gesetzt wird.