Tworzenie unikalnych ograniczeń z kolumnami null

Mam tabelę z takim układem: 

CREATE TABLE Favorites
(
  FavoriteId uuid NOT NULL PRIMARY KEY,
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  MenuId uuid
)

Chcę stworzyć unikalne ograniczenie podobne do tego: 

ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);

Pozwoli to jednak na wiele wierszy z tym samym (UserId, RecipeId), Jeśli MenuId IS NULL. Chcę zezwolić NULL in MenuId na przechowywanie ulubionych, które nie ma powiązanego menu, ale chcę tylko co najwyżej jednego z tych wierszy na parę użytkownik / przepis.

Pomysły, które mam do tej pory to:

  1. Zamiast null należy użyć jakiegoś twardo zakodowanego identyfikatora UUID (takiego jak wszystkie zera).
    Jednakże MenuId ma ograniczenie FK w menu każdego użytkownika, więc musiałbym stworzyć specjalne menu "null" dla każdego użytkownika, co jest kłopotliwe.

  2. Sprawdź istnienie wpisu null, używając zamiast tego wyzwalacza.
    Myślę, że jest to kłopot i lubię unikać wyzwalaczy, gdzie to możliwe. Ponadto nie ufam im, że zagwarantują, że moje dane nigdy nie będą w złym stanie.

  3. Wystarczy o tym zapomnieć i sprawdzić wcześniejsze istnienie wpisu null w środku-ware lub w funkcji insert, a nie miej tego ograniczenia.

[7]}używam Postgres 9.0. Czy jest jakaś metoda, którą przeoczyłem?
Author: Erwin Brandstetter, 2011-11-28
Source

4 answers

Utwórz dwa indeksy częściowe:

CREATE UNIQUE INDEX favo_3col_uni_idx ON favorites (user_id, menu_id, recipe_id)
WHERE menu_id IS NOT NULL;

CREATE UNIQUE INDEX favo_2col_uni_idx ON favorites (user_id, recipe_id)
WHERE menu_id IS NULL;

W ten sposób może być tylko jedna kombinacja (user_id, recipe_id) Gdzie menu_id jest NULL, skutecznie implementując pożądane ograniczenie.

Możliwe wady: nie możesz mieć klucza obcego odwołującego się do (user_id, menu_id, recipe_id) w ten sposób nie możesz oprzeć CLUSTER na indeksie częściowym, a zapytania bez dopasowanego warunku WHERE nie mogą używać indeksu częściowego.

Wydaje się mało prawdopodobne, że chciałbyś mieć odniesienia FK o szerokości trzech kolumn (użyj Kolumna PK zamiast). Jeśli potrzebujesz pełnego indeksu , możesz alternatywnie odrzucić warunek WHERE z favo_3col_uni_idx, a twoje wymagania są nadal egzekwowane.
Indeks, obejmujący teraz całą tabelę, pokrywa się z drugą i staje się większy. W zależności od typowych zapytań i procentu wartości NULL, może to być przydatne lub nie. W ekstremalnych sytuacjach może nawet pomóc utrzymanie obu wersji favo_3col_uni_idx.

Na bok: radzę nie używać mieszanych identyfikatorów przypadków w PostgreSQL .

 270
Author: Erwin Brandstetter,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-02-01 03:11:21

Możesz utworzyć unikalny Indeks z koalesce na MenuId: 

CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);
Musisz tylko wybrać UUID dla KOALESCE, które nigdy nie wystąpi w"prawdziwym życiu". Prawdopodobnie nigdy nie zobaczysz zerowego UUID w prawdziwym życiu, ale możesz dodać ograniczenie czeku, jeśli masz paranoję (a ponieważ oni naprawdę chcą cię dopaść...): 
alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')
 45
Author: mu is too short,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-11-27 23:17:15

Możesz przechowywać ulubione bez powiązanego menu w oddzielnej tabeli: 

CREATE TABLE FavoriteWithoutMenu
(
  FavoriteWithoutMenuId uuid NOT NULL, --Primary key
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  UNIQUE KEY (UserId, RecipeId)
)
 2
Author: ypercubeᵀᴹ,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-11-27 21:29:51

Myślę, że jest tu problem semantyczny. Moim zdaniem Użytkownik może mieć (ale tylko jeden) ulubiony przepis do przygotowania konkretnego menu. (OP ma pomieszane menu i przepis; jeśli się mylę: proszę wymienić MenuId i RecipeId poniżej) Oznacza to, że {user, menu} powinien być unikalnym kluczem w tej tabeli. I powinno wskazywać na dokładnie jeden przepis. Jeśli użytkownik nie ma ulubionej receptury dla tego konkretnego menu , nie powinien istnieć wiersz dla tej pary kluczy {user, menu}. Także: the klucz zastępczy (Favoriteid) jest zbędny: złożone klucze podstawowe są doskonale poprawne dla tabel mapowania relacyjnego.

To prowadziłoby do zredukowanej definicji tabeli: 

CREATE TABLE Favorites
( UserId uuid NOT NULL REFERENCES users(id)
, MenuId uuid NOT NULL REFERENCES menus(id)
, RecipeId uuid NOT NULL REFERENCES recipes(id)
, PRIMARY KEY (UserId, MenuId)
);
 0
Author: wildplasser,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-11-27 23:22:50