Odpowiedź.

W relacyjnych bazach danych zadanie zapewnienia unikalności często polega na ograniczeniach UNIQUE. Jednak w praktyce biznesowej zdarzają się sytuacje, w których unikalność wymagana jest w oparciu o kombinację pól, z których część może być NULL (na przykład unikalna kombinacja email+telefon, gdzie telefon może być nieznany).

Historia pytania: W SQL standardowe OGRANICZENIE UNIKALNOŚCI nie gwarantuje unikalności, gdy jeden z kolumn jest NULL — specyfikacja traktuje takie wartości jako wzajemnie wykluczające się.

Problem: Dla złożonych kluczy z NULL standardowe ograniczenia prowadzą do pojawienia się duplikatów. Jest to szczególnie krytyczne, gdy integralność danych jest konieczna: podczas importu, migracji, masowych aktualizacji.

Rozwiązanie: Użyj kolumny obliczeniowej, która uwzględnia wszystkie wartości (w tym NULL), i nałóż unikalność na tę kolumnę lub użyj logiki wyzwalacza.

Przykład kodu:

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);

Lub (dla PostgreSQL, gdzie istnieją wyrażenia w indeksie i NULL są traktowane jako różne):

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

Kluczowe cechy:

Jawne zarządzanie zasadą unikalności dla kombinacji z NULL.
Możliwość automatyzacji i wsparcia na poziomie bazy danych.
Wsparcie nawet dla złożonych i zmiennych schematów.

Pytania z pułapką.

Czy można użyć zwykłego indeksu UNIQUE do zapewnienia unikalności, jeśli część kolumn dopuszcza NULL?

Nie. Zgodnie z ANSI SQL indeks UNIQUE dopuszcza wiele wierszy, w których przynajmniej jedna z kolumn w kombinacji jest NULL, ponieważ NULL nie jest uważany za równy żadnej innej wartości, w tym NULL.

Jaka jest różnica między używaniem unikalnego indeksu na wyrażeniu a używaniem wyzwalacza BEFORE INSERT do sprawdzania unikalności?

Unikalny indeks jest prostszy do utrzymania i szybszy w działaniu, ale nie zawsze może zrealizować złożone zasady biznesowe (na przykład wyjątki lub niestandardowe kombinacje). Wyzwalacz jest elastyczniejszy, ale wolniejszy i trudniejszy do utrzymania.

Przykład:

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 'Znaleziono duplikat';
  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();

Czy można użyć DISTINCT w SELECT, aby na poziomie aplikacji rozwiązać problem unikalności?

Można, ale tylko dla selekcji — to nie zapobiega wprowadzaniu duplikatów podczas modyfikacji danych i nie jest rozwiązaniem dla ograniczeń na poziomie tabeli.

Typowe błędy i antywzorce

Próby używania OGRANICZENIA UNIKALNOŚCI tam, gdzie kluczem jest kombinacja z możliwymi NULL.
Sprawdzanie unikalności tylko na poziomie aplikacji (omijając bazę danych).
Nadmierna złożoność wyzwalaczy bez jawnych indeksów — utrata wydajności.

Przykład z życia

Negatywny przypadek

Firma wdraża unikalność email+telefon poprzez UNIQUE(email, phone). W bazie pojawiają się duplikaty po email, jeśli telefon=NULL.

Zalety:

Prosta wdrożenie.

Wady:

Utrata integralności, duplikaty.
Niejasne błędy, trudne do śledzenia w analizie.

Pozytywny przypadek

Używana jest kolumna obliczeniowa (COALESCE(email, '##') + '#' + COALESCE(phone, '')), na którą nakładany jest unikalny indeks.