如何创建带空列的唯一约束?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (10)

我有一个表,上面有这样的布局:

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.要存储没有关联菜单的收藏,但我只希望每个用户/菜谱对最多只需要这些行中的一行。

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

  1. 使用一些硬编码的UUID(如所有零)而不是NULL。 然而,MenuId在每个用户的菜单上都有一个FK约束,所以我必须为每个用户创建一个特殊的“NULL”菜单,这是一个麻烦。
  2. 使用触发器检查是否存在空项。 我认为这是一个麻烦,我喜欢尽可能避免触发。
  3. 只需忘记它,并检查以前是否存在中间程序或插入函数中的空项,而不具有此约束。
提问于
用户回答回答于

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为NULL,有效地实现了所需的约束。

用户回答回答于

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

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

您只需要为“现实生活”中永远不会发生的合并选择一个UUID。在现实生活中,您可能永远不会看到零UUID,但是如果您偏执,可以添加一个检查约束:

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

扫码关注云+社区