使用空列创建惟一约束

I have a table with this layout:

我有一张这样布局的桌子:

CREATE TABLE Favorites
(
  FavoriteId uuid NOT NULL PRIMARY KEY,
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  MenuId uuid
)

I want to create a unique constraint similar to this:

我想创建一个与此类似的唯一约束:

ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);

However, this will allow multiple rows with the same (UserId, RecipeId), if MenuId IS NULL. I want to allow NULL in MenuId to store a favorite that has no associated menu, but I only want at most one of these rows per user/recipe pair.

但是，如果MenuId为空，这将允许有多个具有相同(UserId、RecipeId)的行。我想让MenuId中的NULL存储一个没有关联的菜单，但是我最多只需要每个用户/配方对中的一个行。

The ideas I have so far are:

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

Use some hard-coded UUID (such as all zeros) instead of null.
However, MenuId has a FK constraint on each user's menus, so I'd then have to create a special "null" menu for every user which is a hassle.

使用一些硬编码的UUID(比如所有的0)代替null。然而，MenuId在每个用户的菜单上都有FK约束，因此我必须为每个用户创建一个特殊的“null”菜单，这很麻烦。
Check for existence of a null entry using a trigger instead.
I think this is a hassle and I like avoiding triggers wherever possible. Plus, I don't trust them to guarantee my data is never in a bad state.

使用触发器检查是否存在空条目。我觉得这很麻烦，我喜欢尽可能避免触发。另外，我不相信他们能保证我的数据永远不会处于糟糕状态。
Just forget about it and check for the previous existence of a null entry in the middle-ware or in a insert function, and don't have this constraint.

忘掉它，检查中间件或插入函数中是否存在空条目，不要有这个约束。

I'm using Postgres 9.0.

我用9.0 Postgres。

Is there any method I'm overlooking?

有什么方法是我忽略的吗?

4 个解决方案

#1

245

Create 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;

This way, there can only be one combination of (user_id, recipe_id) where menu_id IS NULL, effectively implementing the desired constraint.

这样，只有一个组合(user_id、recipe_id)，其中menu_id为NULL，可以有效地实现所需的约束。

Possible drawbacks: you cannot have a foreign key referencing (user_id, menu_id, recipe_id) this way, you cannot base CLUSTER on a partial index, and queries without a matching WHERE condition cannot use the partial index.

可能的缺点:不能使用外键引用(user_id、menu_id、recipe_id)，不能将集群建立在部分索引的基础上，如果查询没有匹配条件，则不能使用部分索引。

It seems unlikely you'd want a FK reference three columns wide (use the PK column instead). If you need a complete index, you can alternatively drop the WHERE condition from favo_3col_uni_idx and your requirements are still enforced.
The index, now comprising the whole table, overlaps with the other one and gets bigger. Depending on typical queries and the percentage of NULL values, this may or may not be useful. In extreme situations it might even help to maintain both versions of favo_3col_uni_idx.

似乎不太可能希望FK引用宽为3列(使用PK列)。如果您需要一个完整的索引，您可以选择从favo_3col_uni_idx删除WHERE条件，您的需求仍然被强制执行。索引，现在组成了整个表，与另一个重叠并变得更大。根据典型查询和NULL值的百分比，这可能有用，也可能没有用。在极端情况下，它甚至可能帮助维护两个版本的favo_3col_uni_idx。

Aside: I advise not to use mixed case identifiers in PostgreSQL.

旁白:我建议不要在PostgreSQL中使用混合大小写标识符。

#2

You could create a unique index with a coalesce on the MenuId:

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

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

You'd just need to pick a UUID for the COALESCE that will never occur in "real life". You'd probably never see a zero UUID in real life but you could add a CHECK constraint if you are paranoid (and since they really are out to get you...):

您只需要为“现实生活”中永远不会出现的合并选择一个UUID。在现实生活中，你可能永远不会看到一个零的UUID，但是如果你是多疑的，你可以添加一个检查约束(因为他们真的是为了让你…)

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

#3

You can store favourites with no associated menu in a separate table:

你可以在单独的表格中存储没有相关菜单的收藏:

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

#4

I think there is a semantic problem here. In my view, a user can have a (but only one) favourite recipe to prepare a specific menu. (The OP has menu and recipe mixed up; if I am wrong: please interchange MenuId and RecipeId below) That implies that {user,menu} should be a unique key in this table. And it should point to exactly one recipe. If the user has no favourite recipe for this specific menu no row should exist for this {user,menu} key pair. Also: the surrogate key (FaVouRiteId) is superfluous: composite primary keys are perfectly valid for relational-mapping tables.

我认为这里存在一个语义问题。在我看来，用户可以有一个(但只有一个)最喜欢的食谱来准备特定的菜单。(OP菜单和配方混合在一起;如果我错了:请在下面交换MenuId和RecipeId)，这意味着{user,menu}应该是这个表中唯一的键。它应该指向一个食谱。如果用户对这个特定的菜单没有最喜欢的食谱，那么这个{user,menu}对不应该存在任何行。另外:代理键(preferteid)是多余的:复合主键对于关系映射表是完全有效的。

That would lead to the reduced table definition:

这将导致表的定义减少:

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)
);

#1

245