使用空列创建唯一约束

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)if MenuId IS NULL。我想允许NULL在MenuId存储不具有关联菜单中的最爱，但我只希望每个用户/食谱对这些行中最多只有一个。

到目前为止，我的想法是：

1. 使用一些硬编码的UUID（例如全零）而不是null。
   但是，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)where的一种组合menu_id IS NULL，从而有效地实现所需的约束。

可能的缺点：您不能有外键引用(user_id, menu_id, recipe_id)，不能CLUSTER基于部分索引，并且没有匹配WHERE条件的查询不能使用部分索引。（似乎不太可能希望FK引用宽三列-
改用PK列）。

如果您需要一个 完整的 索引，则可以从中删除WHERE条件，favo_3col_uni_idx并且仍然可以执行您的要求。
现在组成整个表的索引与另一个表重叠，并且变得更大。根据典型的查询和NULL值的百分比，这可能有用也可能没有用。在极端情况下，甚至可以帮助维护所有三个索引（两个局部索引和总索引排在最前面）。

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