使用空列创建唯一约束

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（例如全零）而不是 null。

但是， MenuId 对每个用户的菜单都有 FK 约束，所以我必须为每个用户创建一个特殊的“空”菜单，这很麻烦。

1. 改为使用触发器检查是否存在空条目。

我认为这很麻烦，我喜欢尽可能避免触发。另外，我不相信他们能保证我的数据永远不会处于不良状态。

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

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

原文由 Mike Christensen 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

Postgres 15 或更新版本

Postgres 15（当前为测试版）添加了子句 NULLS NOT DISTINCT 。 发行说明：

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

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

使用此子句 NULL 被视为只是另一个值，并且 UNIQUE 约束 不允许超过一行具有相同的 NULL 值。现在的任务很简单：

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

手册章节 “唯一约束” 中有示例。

该子句切换 所有 索引键的行为。您不能将 NULL 视为一个键相等，而另一个键则不相等。

NULLS DISTINCT 保持默认（与标准 SQL 一致）并且不必拼写。

相同的子句也适用于 UNIQUE 索引：

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

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

Postgres 14 岁或以上

创建 两个部分索引：

 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) 。 （您似乎不太可能想要三列宽的 FK 引用 - 请改用 PK 列！）
• 您不能将 CLUSTER 基于部分索引。
• 没有匹配 WHERE 条件的查询不能使用部分索引。

如果您需要一个 完整 的索引，您也可以从 favo_3col_uni_idx 中删除 WHERE 条件，您的要求仍然得到执行。

现在包含整个表的索引与另一个表重叠并变大。根据典型查询和 NULL 值的百分比，这可能有用也可能没用。在极端情况下，它甚至可能有助于维护所有三个索引（两个部分索引和顶部的总索引）。

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

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

另外：我建议不要 在 PostgreSQL 中使用混合大小写标识符。

原文由 Erwin Brandstetter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

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

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

原文由 ypercubeᵀᴹ 发布，翻译遵循 CC BY-SA 3.0 许可协议