使用空列创建唯一约束

Postgres 15 或更新版本（目前为测试版）

Postgres 15 添加了子句 NULLS NOT DISTINCT。 The release notes:

允许唯一约束和索引将 NULL 值视为不明确的 (Peter Eisentraut) 以前，NULL 值始终被索引为不同的值，但现在可以通过使用 UNIQUE NULLS NOT DISTINCT 创建约束和索引来更改这一点。

在此子句中，NULL 被视为只是另一个值，并且 UNIQUE constraint 不允许多行具有相同的 NULL 值。现在的任务很简单：

ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);

手册第 "Unique Constraints" 章中有示例。
该子句切换 所有 索引键的行为。您不能将 NULL 视为一个键相等，而另一个键则不相等。
NULLS DISTINCT 仍然是默认值（与标准 SQL 一致）并且不必拼写出来。

相同的子句也适用于 UNIQUE index：

CREATE UNIQUE INDEX favo_uni_idx
ON favorites (user_id, menu_id, recipe_id) NULLS NOT DISTINCT;

注意新子句在关键字段之后的位置。

Postgres 14 岁或以上

创建 two partial indexes：

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) where menu_id IS NULL 的一种组合，有效地实现了所需的约束。

可能的缺点：

您不能有外键引用（user_id、menu_id、recipe_id）。 （您似乎不太可能想要三列宽的 FK 引用 - 请改用 PK 列！）

您不能将 CLUSTER 基于部分索引。

没有匹配 WHERE 条件的查询不能使用部分索引。

如果您需要一个完整索引，您也可以从 favo_3col_uni_idx 中删除 WHERE 条件，您的要求仍然有效。
现在包含整个表的索引与另一个重叠一并变大。根据典型查询和 NULL 值的百分比，这可能有用也可能没用。在极端情况下，它甚至可能有助于维护所有三个索引（两个部分索引和顶部的总索引）。

对于单个可空列（可能是两个），这是一个很好的解决方案。但是它很快就会失控，因为您需要为每个可为空列的组合使用单独的部分索引，因此该数字呈二项式增长。对于多个可为空的列，请参阅：

为什么我的 UNIQUE 约束没有触发？

另外：我建议不要使用 mixed case identifiers in PostgreSQL。

---

您可以在 MenuId 上创建具有合并的唯一索引：

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

您只需要为“现实生活”中永远不会出现的 COALESCE 选择一个 UUID。在现实生活中你可能永远不会看到零 UUID，但如果你是偏执狂，你可以添加一个 CHECK 约束（因为他们真的很想得到你......）：

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

---

您可以将没有关联菜单的收藏夹存储在单独的表中：

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

---

我认为这里存在语义问题。在我看来，用户可以有一个（但只有一个）最喜欢的食谱来准备一个特定的菜单。 （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)
);

---