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

Question

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

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 (например, все нули) вместо нуля.
   Тем не мение, MenuId имеет ограничения FK для меню каждого пользователя, поэтому мне нужно будет создать специальное "нулевое" меню для каждого пользователя, что создает трудности.

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

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

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

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

Answer 1

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

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 IS NULL, эффективно реализующий желаемое ограничение.

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

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

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

Answer 2

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

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

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

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

Answer 3

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

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

Answer 4

Я считаю, что есть вариант, который объединяет предыдущие ответы в более оптимальное решение.

      create table unique_with_nulls (
    id serial not null,
    name varchar not null,
    age int2 not null,
    email varchar,
    email_discriminator varchar not null generated always as ( coalesce(email::varchar, 0::varchar) ) stored,

    constraint uwn_pkey primary key (id)
);

create unique index uwn_name_age_email_uidx on unique_with_nulls(name, age, email_discriminator);

Здесь происходит то, что столбец будет сгенерирован во время «вставки или обновления» либо как фактическое электронное письмо, либо как «0», если предыдущий имеет значение null. Затем ваш уникальный индекс должен быть нацелен на столбец дискриминатора.
Таким образом, нам не нужно создавать два частичных индекса, и мы не теряем возможность использовать индексированное сканирование наnameиageтолько отбор.
Кроме того, вы можете сохранить тип столбца, и у нас не возникнет проблем с функцией объединения, потому что это не внешний ключ. И вам не нужно беспокоиться о том, что этот столбец получит неожиданные значения, потому что сгенерированные столбцы не могут быть записаны.

Я вижу три упрямых недостатка в этом решении, но все они подходят для моих нужд:

• дублирование данных междуemail_discriminator.
• тот факт, что я должен писать в колонку и читать из другой.
• необходимость найти значение, выходящее за пределы множества допустимых значенийemailбыть резервным (и иногда это может быть трудно найти или даже субъективно).

Answer 5

Я думаю, что здесь есть семантическая проблема. На мой взгляд, пользователь может иметь (но только один) любимый рецепт для приготовления определенного меню. (У OP есть меню и рецепт, перепутанные; если я не прав: пожалуйста, поменяйте местами MenuId и RecipeId ниже) Это означает, что {user, menu} должен быть уникальным ключом в этой таблице. И это должно указывать ровно на один рецепт. Если у пользователя нет любимого рецепта для этого конкретного меню, для этой пары клавиш {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)
);