Создать уникальное ограничение с пустыми столбцами

у меня есть таблица с этого макета:

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

Я хочу создать уникальное ограничение, подобное этому:

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

однако, это позволит несколько строк с тем же (UserId, RecipeId), Если MenuId IS NULL. Я хочу, чтобы NULL на MenuId чтобы сохранить избранное, которое не имеет связанного меню, но я хочу только не более одной из этих строк на пару пользователь/рецепт.

идеи, которые у меня есть до сих пор:

  1. использовать некоторые жестко запрограммированные идентификаторы UUID (например, все нули) вместо null.
    Однако,MenuId имеет ограничение FK на меню каждого пользователя, поэтому мне придется создать специальное меню "null" для каждого пользователя, что является хлопот.

  2. проверьте наличие нулевой записи, используя вместо этого триггер.
    Я думаю, что это хлопот, и мне нравится избегать триггеров, где это возможно. Кроме того, я не доверяю им, чтобы гарантировать, что мои данные никогда не находятся в плохом состоянии.

  3. просто забудьте об этом и проверьте предыдущее существование нулевой записи в среднем изделии или в функции вставки и не имеет этого ограничения.

Я использую Postgres 9.0.

есть ли какой-либо метод, который я пропускаю?

4 169
sql postgresql notnull database-design referential-integrity

4 ответа:

создать два частичных индекса:

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;

таким образом, может быть только одна комбинация (user_id, recipe_id) здесь menu_id имеет значение NULL, эффективно реализуя желаемое ограничение.

возможные недостатки: у вас не может быть ссылки на внешний ключ (user_id, menu_id, recipe_id) таким образом, вы не можете основывать CLUSTER на частичный индекс, и запросы без соответствующего WHERE условие не может использовать частичный индекс.

это кажется маловероятным вам понадобится ссылка FK шириной в три столбца (вместо этого используйте столбец PK). Если вам нужен полное индекс, вы можете альтернативно отбросить WHERE условия favo_3col_uni_idx и ваши требования по-прежнему соблюдаются.
Индекс, теперь состоящий из всей таблицы, перекрывается с другим и становится больше. В зависимости от типичных запросов и процент NULL значения, это может быть или не быть полезным. В экстремальных ситуациях это может даже помочь поддерживать обе версии favo_3col_uni_idx.

в сторону: я советую не использовать смешанные идентификаторы регистра в PostgreSQL.

270

вы можете создать уникальный индекс с объединением на MenuId:

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

вам просто нужно выбрать UUID для объединения, которое никогда не произойдет в "реальной жизни". Вы, вероятно, никогда не увидите нулевой UUID в реальной жизни, но вы можете добавить ограничение проверки, если вы параноик (и так как они действительно, чтобы получить вас...):

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

вы можете хранить избранное без связанного меню в отдельной таблице:

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

Я думаю, что здесь есть семантическая проблема. На мой взгляд, пользователь может иметь (но единственный) любимый рецепт для приготовления конкретного меню. (У OP есть меню и рецепт, смешанный; если я ошибаюсь: пожалуйста, поменяйте MenuId и RecipeId ниже) Это означает, что {user, menu} должен быть уникальным ключом в этой таблице. И это должно указывать на ровно рецепт. Если у пользователя нет любимого рецепта для этого конкретного меню ни одна строка не должна существовать для этого {пользователь, меню} пара клавиш. Кроме того: суррогатный ключ (FaVouRiteId) является излишним: составные первичные ключи идеально подходят для таблиц реляционного сопоставления.

это приведет к уменьшению определения таблицы:

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