Utwórz unikalne ograniczenie z pustymi kolumnami

Mam stół z tym układem:

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

Chcę utworzyć 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ę pozwolić NULLna MenuIdprzechowywanie faworyta, który nie ma menu powiązanych, ale chcę tylko co najwyżej jeden z tych wierszy na parę użytkownik / receptury.

Dotychczasowe pomysły to:

1. Użyj jakiegoś zakodowanego UUID (takiego jak wszystkie zera) zamiast null.
   Jednak MenuIdma ograniczenie FK w menu każdego użytkownika, więc musiałbym stworzyć specjalne menu „zerowe” dla każdego użytkownika, co jest kłopotliwe.

2. Sprawdź, czy istnieje wpis zerowy, używając wyzwalacza.
   Myślę, że to kłopot i lubię unikać wyzwalaczy tam, gdzie to możliwe. Ponadto nie ufam im, aby zagwarantować, że moje dane nigdy nie będą w złym stanie.

3. Po prostu zapomnij o tym i sprawdź wcześniejsze istnienie pustego wpisu w oprogramowaniu pośrednim lub w funkcji wstawiania i nie masz tego ograniczenia.

Używam Postgres 9.0.

Czy przeoczyłem jakąś metodę?

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 istnieć tylko jedna kombinacja (user_id, recipe_id)gdzie menu_id IS NULL, skutecznie wdrażając pożądane ograniczenie.

Możliwe wady: nie możesz mieć odwołania do klucza obcego (user_id, menu_id, recipe_id), nie możesz bazować CLUSTERna indeksie częściowym, a zapytania bez pasującego WHEREwarunku nie mogą używać indeksu częściowego. (Wydaje się mało prawdopodobne, abyś chciał mieć odniesienie FK o szerokości trzech kolumn - zamiast tego użyj kolumny PK).

Jeśli potrzebujesz pełnego indeksu, możesz alternatywnie usunąć WHEREwarunek, favo_3col_uni_idxa Twoje wymagania są nadal egzekwowane.
Indeks, który obejmuje teraz całą tabelę, pokrywa się z drugą tabelą i staje się większy. W zależności od typowych zapytań i odsetka NULLwartości może to być przydatne lub nie. W skrajnych sytuacjach może nawet pomóc utrzymać wszystkie trzy indeksy (dwa częściowe i suma na górze).

Poza tym: Radzę nie używać identyfikatorów mieszanych w PostgreSQL .

Możesz stworzyć unikalny indeks z koalescencją w MenuId:

CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);

Musisz po prostu wybrać UUID dla WĘGLA, który nigdy nie nastąpi w „prawdziwym życiu”. Prawdopodobnie nigdy nie zobaczyłbyś zerowego UUID w prawdziwym życiu, ale możesz dodać ograniczenie CHECK, jeśli jesteś paranoikiem (a ponieważ oni naprawdę chcą cię dostać ...):

alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')

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

CREATE TABLE FavoriteWithoutMenu
(
  FavoriteWithoutMenuId uuid NOT NULL, --Primary key
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  UNIQUE KEY (UserId, RecipeId)
)

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

Doprowadziłoby to do ograniczenia 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)
);