创建具有空列的唯一约束
我有一个布局是这样的表：
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存储一个没有关联菜单的收藏夹，但我只希望每个用户/菜谱对最多只有一行。
到目前为止，我的想法是：
- 使用一些硬编码的UUID (例如全零)而不是null。
但是，MenuId在每个用户的菜单上都有一个FK约束，所以我必须为每个用户创建一个特殊的" null“菜单，这很麻烦。
- 使用触发器检查是否存在null条目。
我认为这是一个麻烦，我喜欢尽可能避免触发器。此外，我不相信他们能保证我的数据永远不会处于糟糕的状态。
- 忘记它，检查中间件或插入函数中以前是否存在null条目，并且没有这个约束。
我使用的是Postgres 9.0。
有没有什么我忽略的方法？
回答 4
Stack Overflow用户
发布于 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。
Stack Overflow用户
发布于 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')Stack Overflow用户
发布于 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
复制相似问题
腾讯云开发者
