使用空列创建唯一约束

我有一张具有以下布局的桌子：

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), if MenuId IS NULL。我想允许NULL in MenuId存储没有关联菜单的收藏夹，但我只需要每个用户/食谱对最多其中一行。

到目前为止我的想法是：

1. 使用一些硬编码的 UUID（例如全零）而不是 null。
   然而，MenuId每个用户的菜单都有 FK 约束，所以我必须为每个用户创建一个特殊的“空”菜单，这很麻烦。

2. 使用触发器检查是否存在空条目。
   我认为这很麻烦，我喜欢尽可能避免触发因素。另外，我不相信他们能保证我的数据永远不会处于不良状态。

3. 只需忘记它并检查中间件或插入函数中先前是否存在空条目即可，并且没有此约束。

我正在使用 Postgres 9.0。有什么我忽略的方法吗？

Postgres 15 或更高版本

Postgres 15 添加了该子句NULLS NOT DISTINCT. 发行说明： https://www.postgresql.org/docs/15/release-15.html#id-1.11.6.5.5.3.4

• 允许唯一约束和索引将 NULL 值视为不不同 (Peter Eisentraut)

  以前 NULL 值总是被索引为不同的值，但是 现在可以通过使用创建约束和索引来更改UNIQUE NULLS NOT DISTINCT.

有了这个条款null被视为只是另一个值，并且UNIQUE约束 https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS不允许多于一行具有相同的内容null价值。现在任务很简单：

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

手册章节里有例子“独特的约束” https://www.postgresql.org/docs/15/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS.
该子句切换行为all相同索引的键。你无法治疗null对于一个键来说相等，但对于另一个键则不相等。
NULLS DISTINCT保留默认值（与标准 SQL 一致）并且不必拼写出来。

相同的子句适用于UNIQUE index https://www.postgresql.org/docs/15/sql-createindex.html, too:

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

注意新子句的位置after关键领域。

Postgres 14 或以上

Create 两个部分索引 https://www.postgresql.org/docs/current/indexes-partial.html:

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条件不能使用部分索引。

如果您需要一个complete索引，您也可以删除WHERE条件来自favo_3col_uni_idx并且您的要求仍然得到执行。
该索引现在包含整个表，与另一个索引重叠并变得更大。取决于典型的查询和百分比null值，这可能有用也可能没用。在极端情况下，它甚至可能有助于维护所有三个索引（两个部分索引和顶部的总计索引）。

这是一个很好的解决方案单个可为空列，也许适合两个人。但它很快就会失控，因为您需要为每个可为空列的组合建立一个单独的部分索引，因此该数字呈二项式增长。为了多个可为空的列，请参阅：

• 为什么我的 UNIQUE 约束没有触发？ https://dba.stackexchange.com/a/299107/3684

旁白：我建议不要使用PostgreSQL 中的混合大小写标识符 https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS.