Dlaczego ograniczenie UNIKALNE dopuszcza tylko jedną wartość NULL?

Technicznie NULL = NULL jest fałszem, zgodnie z tą logiką żaden NULL nie jest równy żadnemu NULL, a wszystkie wartości NULL są różne. Czy nie powinno to oznaczać, że wszystkie wartości NULL są unikalne, a unikalny indeks powinien dopuszczać dowolną liczbę wartości NULL?

Dlaczego to działa w ten sposób? Ponieważ dawno temu, ktoś podjął decyzję projektową, nie wiedząc ani nie dbając o to, co mówi standard (w końcu mamy różne dziwne zachowania z NULLsi i możemy wymuszać różne zachowania do woli). Decyzja ta podyktowana, że w tym przypadku NULL = NULL.

To nie była zbyt mądra decyzja. Powinni byli zrobić, aby domyślne zachowanie było zgodne ze standardem ANSI, a jeśli naprawdę chcieli tego szczególnego zachowania, pozwól mu na skorzystanie z opcji DDL, takiej jak WITH CONSIDER_NULLS_EQUALlub WITH ALLOW_ONLY_ONE_NULL.

Oczywiście z perspektywy czasu 20/20.

I tak mamy teraz obejście, nawet jeśli nie jest to najczystsze lub najbardziej intuicyjne.

Prawidłowe zachowanie ANSI można uzyskać w programie SQL Server 2008 i nowszych, tworząc unikalny, filtrowany indeks.

CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;

Pozwala to na więcej niż jedną NULLwartość, ponieważ wiersze te są całkowicie pomijane przy sprawdzaniu duplikatów. Jako dodatkowy bonus, stałoby się to mniejszym indeksem niż ten, który składałby się z całej tabeli, gdyby dopuszczono wiele NULLs (szczególnie, gdy nie jest to jedyna kolumna w indeksie, ma INCLUDEkolumny itp.). Jednak możesz chcieć wiedzieć o niektórych innych ograniczeniach filtrowanych indeksów:

• Jak filtrowane indeksy mogą być bardziej wydajną funkcją

Poprawny. Implementacja unikalnego ograniczenia lub indeksu w serwerze SQL pozwala na jeden i tylko jeden NULL. Popraw również, że to technicznie nie pasuje do definicji NULL, ale jest to jedna z tych rzeczy, które zrobili, aby uczynić ją bardziej przydatną, nawet jeśli nie jest „technicznie” poprawna. Uwaga: KLUCZ PODSTAWOWY (także unikalny indeks) nie zezwala na wartości NULL (oczywiście).

Po pierwsze - przestań używać wyrażenia „Wartość zerowa”, po prostu doprowadzi Cię to na manowce. Zamiast tego należy użyć wyrażenia „marker zerowy” - znacznik w kolumnie wskazujący, że rzeczywista wartość w tej kolumnie jest brakująca lub nie ma zastosowania (należy jednak pamiętać, że znacznik nie mówi, która z tych opcji jest rzeczywiście przypadkiem¹).

Teraz wyobraź sobie, co następuje (gdy baza danych nie ma pełnej wiedzy na temat modelowanej sytuacji).

Situation          Database

ID   Code          ID   Code
--   -----         --   -----
1    A             1    A
2    B             2    (null)
3    C             3    C
4    B             4    (null)

Modelowana przez nas zasada uczciwości to „Kod musi być unikalny”. Sytuacja w świecie rzeczywistym to narusza, więc baza danych nie powinna zezwalać na jednoczesne umieszczanie w tabeli zarówno elementów 2, jak i 4.

Najbezpieczniejszym i najmniej elastycznym podejściem byłoby uniemożliwienie zerowania znaczników w polu Kod, więc nie ma możliwości niezgodności danych. Najbardziej elastycznym podejściem byłoby dopuszczenie wielu znaczników zerowych i martwienie się o unikalność przy wprowadzaniu wartości.

Programiści Sybase zastosowali nieco bezpieczne, niezbyt elastyczne podejście polegające na dopuszczeniu tylko jednego znacznika zerowego w tabeli - od tego czasu komentatorzy narzekają. Microsoft kontynuował to zachowanie, chyba dla kompatybilności wstecznej.

¹ Jestem pewien, że czytałem gdzieś, że Codd rozważał wdrożenie dwóch zerowych znaczników - jednego dla nieznanego, drugiego dla niemożliwego do zastosowania - ale odrzucił go, ale nie mogę znaleźć odniesienia. Czy dobrze pamiętam?

PS Mój ulubiony cytat o null: Louis Davidson, „Profesjonalny projekt bazy danych SQL Server 2000”, Wrox Press, 2001, strona 52. „Sprowadzone do jednego zdania: NULL jest złe”.

To może nie być technicznie dokładne, ale filozoficznie pomaga mi spać w nocy ...

Jak wielu innych powiedziało lub nawiązywało do nich, jeśli uważasz, że NULL jest nieznany, nie możesz ustalić, czy jedna wartość NULL jest w rzeczywistości równa innej wartości NULL. Myśląc o tym w ten sposób, wyrażenie NULL == NULL powinno mieć wartość NULL, co oznacza nieznane.

Unikalne ograniczenie wymagałoby ostatecznej wartości do porównania wartości kolumn. Innymi słowy, porównując wartość jednej kolumny z dowolną inną wartością kolumny za pomocą operatora równości, musi ona mieć wartość false, aby była poprawna. Nieznany nie jest tak naprawdę fałszywy, chociaż często jest traktowany jako fałsz. Dwie wartości NULL mogą być równe lub nie ... po prostu nie można definitywnie ustalić.

Pomaga myśleć o wyjątkowym ograniczeniu jako o ograniczających wartościach, które można ustalić, aby się od siebie różniły. Rozumiem przez to, że uruchamiasz SELECT, który wygląda mniej więcej tak:

SELECT * from dbo.table1 WHERE ColumnWithUniqueContraint="some value"

Większość ludzi oczekiwałaby jednego rezultatu, biorąc pod uwagę, że istnieje wyjątkowe ograniczenie. Jeśli zezwoliłeś na wiele wartości NULL w ColumnWithUniqueConstraint, wówczas niemożliwe byłoby wybranie pojedynczego odrębnego wiersza z tabeli przy użyciu NULL jako wartości porównanej.

Biorąc to pod uwagę, uważam, że bez względu na to, czy jest zaimplementowane dokładnie w odniesieniu do definicji NULL, jest zdecydowanie bardziej praktyczne w większości sytuacji niż dopuszczanie wielu wartości NULL.

Jednym z głównych celów UNIQUEograniczenia jest zapobieganie powielaniu rekordów. Jeśli potrzebna jest tabela, w której może istnieć wiele rekordów, w których wartość jest „nieznana”, ale żadne dwa rekordy nie mogą mieć tej samej „znanej” wartości, wówczas nieznanym wartościom należy przypisać sztuczne unikalne identyfikatory przed ich dodane do tabeli.

Istnieje kilka rzadkich przypadków, w których kolumna ma UNIQUEograniczenie i zawiera pojedynczą wartość zerową; na przykład, jeśli tabela zawiera odwzorowanie wartości kolumn i zlokalizowanych opisów tekstowych, wiersz dla NULLumożliwiłby zdefiniowanie opisu, który powinien się pojawiać, gdy kolumna jest w innej tabeli NULL. Zachowanie NULLpozwala na taki przypadek użycia.

W przeciwnym razie nie widzę podstaw dla bazy danych z UNIQUEograniczeniem dla dowolnej kolumny, aby umożliwić istnienie wielu identycznych rekordów, ale nie widzę sposobu, aby temu zapobiec, jednocześnie dopuszczając wiele rekordów, których wartości kluczowych nie można rozróżnić. Stwierdzenie, że NULLnie jest równe sobie, nie spowoduje, że NULLwartości będą się od siebie odróżniać.