Odpowiedź.

Historia pytania

Kontrola unikalności kluczy biznesowych (nie tylko według ID, ale także według „naturalnych” pól) od dawna jest istotna w aplikacjach korporacyjnych. Często takie klucze są złożone (kilka kolumn) i dopuszczają wartości NULL. Standardowe narzędzia SQL — UNIQUE CONSTRAINT lub unikalne indeksy — mają ograniczenia: zgodnie z standardem SQL, kilka wierszy, w których przynajmniej jeden z komponentów klucza UNIQUE jest równy NULL, uznawane są za unikalne i nie naruszają ograniczenia.

Problem

Logika biznesowa może wymagać, aby zestaw kolumn (w tym dopuszczających NULL) był w sumie unikalny, a zachowanie standardu SQL (NULL ≠ NULL) łamie ten scenariusz. Na przykład mamy tabelę z parą kolumn (numer_paszportu, seria_paszportu), i przynajmniej jedna z nich może być NULL, ale jeśli wartości się zgadzają, musimy zabronić duplikatu.

Rozwiązanie

W większości popularnych DBMS rozwiązanie to implementacja sprawdzenia unikalności przez wyzwalacz lub użycie indeksów częściowych z warunkiem, lub korzystanie z funkcji, które pozwalają porównywać NULL jako równe wartości (na przykład ISNULL lub COALESCE). Oto przykład w PostgreSQL z użyciem unikalnego indeksu na wyrażeniu:

CREATE UNIQUE INDEX idx_passport_unique
ON persons (
  COALESCE(numer_paszportu, ''),
  COALESCE(suara_paszportu, '')
);

Na poziomie logiki aplikacji często trzeba duplikować kontrolę, aby nie przegapić duplikatów na etapie wstawiania.

Kluczowe cechy:

Standardowy UNIQUE CONSTRAINT nie działa, jeśli w kluczu uczestniczy przynajmniej jeden NULL.
Rozwiązanie zależy od DBMS: indeksy częściowe, kolumny obliczeniowe, wyzwalacze.
Kontrola unikalności może być nałożona na logikę biznesową w przypadku niemożności utworzenia poprawnego indeksu.

Pytania z pułapką.

Czy można być pewnym, że unikalny indeks gwarantuje niemożność wstawienia duplikatów, jeśli wśród kolumn są kolumny nullable?

Nie, nie można. W SQL zachowanie z NULL jest szczególne: wiersze z identycznym zestawem wartości, gdzie przynajmniej jeden z nich to NULL, indeks traktuje jako różne i zezwala na ich jednoczesne przechowywanie.

Czy różne DBMS mogą realizować unikalność NULL na różne sposoby?

Tak, istnieją różnice między nimi. Na przykład, w Oracle unikalny indeks dopuszcza kilka wierszy z NULL, a w MS SQL — jeden. PostgreSQL może tworzyć indeks częściowy za pomocą wyrażeń.

Czy można obejść problem tylko na poziomie DDL i bez wyzwalaczy?

W niektórych DBMS — tak, za pomocą wyrażeń, na przykład COALESCE. Ale nie zawsze, a jeśli potrzebne jest złożone zachowanie (NULL musi być uważany za duplikat), konieczne będzie użycie sprawdzeń logicznych lub wyzwalaczy.

Typowe błędy i antywzorce

Poleganie na standardowym UNIQUE, gdy w kolumnach dopuszczany jest NULL
Nie sprawdzanie duplikatów na poziomie logiki biznesowej
Tworzenie wyzwalaczy bez uwzględnienia wydajności

Przykład z życia

Negatywny przypadek

W niekrytycznej bazie podjęto decyzję o poleganiu na unikalnym indeksie według (email, kod działu), gdzie oba pola dopuszczają NULL. W rezultacie wielokrotnie pojawiały się duplikaty, przez co psuły się zewnętrzne integracje.

Zalety:

Prosty DDL

Wady:

Nieoczywiste duplikaty, niespójne dane, błędy raportów

Pozytywny przypadek

W PostgreSQL klucz biznesowy z polami (numer paszportu, seria) zrealizowano przez indeks na COALESCE, a dodatkowo dodano kontrolę po stronie aplikacji.