我有一个布局是这样的表:
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);
但是,如果为MenuId IS NULL
,这将允许多个行具有相同的(UserId, RecipeId)
。我希望允许MenuId
中的NULL
存储一个没有关联菜单的收藏夹,但我只希望每个用户/菜谱对最多只有一行。
到目前为止,我的想法是:
但是,MenuId
在每个用户的菜单上都有一个FK约束,所以我必须为每个用户创建一个特殊的" null“菜单,这很麻烦。
我认为这是一个麻烦,我喜欢尽可能避免触发器。此外,我不相信他们能保证我的数据永远不会处于糟糕的状态。
我使用的是Postgres 9.0。
有没有什么我忽略的方法?
发布于 2011-11-28 05:34:57
创建
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。
发布于 2011-11-28 05:45:09
您可以在MenuId上使用合并创建唯一的索引:
CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);
你只需要为在“现实生活”中不会出现的合并选择一个UUID即可。在现实生活中,你可能永远不会看到零UUID,但如果你是偏执狂,你可以添加一个CHECK约束(因为他们真的想要抓住你...):
alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')
发布于 2011-11-28 05:29:52
您可以在单独的表中存储没有关联菜单的收藏夹:
CREATE TABLE FavoriteWithoutMenu
(
FavoriteWithoutMenuId uuid NOT NULL, --Primary key
UserId uuid NOT NULL,
RecipeId uuid NOT NULL,
UNIQUE KEY (UserId, RecipeId)
)
https://stackoverflow.com/questions/8289100
复制相似问题