首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >创建具有空列的唯一约束

创建具有空列的唯一约束
EN

Stack Overflow用户
提问于 2011-11-28 05:16:13
回答 4查看 119.8K关注 0票数 293

我有一个布局是这样的表:

代码语言:javascript
复制
CREATE TABLE Favorites
(
  FavoriteId uuid NOT NULL PRIMARY KEY,
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  MenuId uuid
)

我想创建一个类似下面这样的唯一约束:

代码语言:javascript
复制
ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);

但是,如果为MenuId IS NULL,这将允许多个行具有相同的(UserId, RecipeId)。我希望允许MenuId中的NULL存储一个没有关联菜单的收藏夹,但我只希望每个用户/菜谱对最多只有一行。

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

  1. 使用一些硬编码的UUID (例如全零)而不是null。

但是,MenuId在每个用户的菜单上都有一个FK约束,所以我必须为每个用户创建一个特殊的" null“菜单,这很麻烦。

  • 使用触发器检查是否存在null条目。

我认为这是一个麻烦,我喜欢尽可能避免触发器。此外,我不相信他们能保证我的数据永远不会处于糟糕的状态。

  • 忘记它,检查中间件或插入函数中以前是否存在null条目,并且没有这个约束。

我使用的是Postgres 9.0。

有没有什么我忽略的方法?

EN

回答 4

Stack Overflow用户

回答已采纳

发布于 2011-11-28 05:34:57

创建

代码语言:javascript
复制
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条件的部分索引上,也不能使用部分索引。

如果您需要一个完整的索引,您也可以从favo_3col_uni_idx中删除WHERE条件,但您的要求仍然是强制的。

索引,现在组成了整个表,与另一个重叠,并变得更大。这取决于典型的查询和NULL值的百分比,这可能有用,也可能无用。在极端情况下,维护所有三个索引(两个部分索引和顶部的总索引)可能会有所帮助。

对于单个可空的列来说,这是一个很好的解决方案,也许可以是两个。但是对于更多的列,它很快就失去了控制,因为您需要为每个可空列的组合使用单独的部分索引,因此这个数字会以二项式增长。有关多个可空的列的信息,请参阅:

旁白:我建议不要使用mixed case identifiers in PostgreSQL

票数 443
EN

Stack Overflow用户

发布于 2011-11-28 05:45:09

您可以在MenuId上使用合并创建唯一的索引:

代码语言:javascript
复制
CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);

你只需要为在“现实生活”中不会出现的合并选择一个UUID即可。在现实生活中,你可能永远不会看到零UUID,但如果你是偏执狂,你可以添加一个CHECK约束(因为他们真的想要抓住你...):

代码语言:javascript
复制
alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')
票数 93
EN

Stack Overflow用户

发布于 2011-11-28 05:29:52

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

代码语言:javascript
复制
CREATE TABLE FavoriteWithoutMenu
(
  FavoriteWithoutMenuId uuid NOT NULL, --Primary key
  UserId uuid NOT NULL,
  RecipeId uuid NOT NULL,
  UNIQUE KEY (UserId, RecipeId)
)
票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/8289100

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档