使用空列创建唯一约束-Java 学习之路

179

我有一个这种布局的表：

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 . 我想允许 MenuId 中的 NULL 存储没有关联菜单的收藏夹，但我最多只想要每个用户/配方对中的一行 .

我到目前为止的想法是：

使用一些硬编码的UUID（例如全零）而不是null .
但是， MenuId 对每个用户都有一个FK约束's menus, so I' d然后必须为每个用户创建一个特殊的"null"菜单，这是一个麻烦 .
使用触发器检查是否存在空条目 .
我认为这是一个麻烦，我喜欢尽可能避免触发器 . 另外，我不相信他们能保证我的数据永远不会处于不良状态 .
只需忘记它并检查中间件或插入函数中是否存在空条目，并且没有此约束 .

我正在使用Postgres 9.0 .

我有什么方法可以忽略吗？

4 回答

2
创建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;
```
这样，只能有一个 (user_id, recipe_id) 的组合，其中 menu_id IS NULL，有效地实现了所需的约束 .

可能的缺点：你不能以这种方式引用 (user_id, menu_id, recipe_id) 的外键，你不能在部分索引上 Build CLUSTER ，而没有匹配 WHERE 条件的查询不能使用部分索引 .

您似乎不太可能希望FK引用三列宽（使用PK列代替） . 如果您需要完整索引，则可以从 favo_3col_uni_idx 中删除 WHERE 条件，并且仍然会强制执行您的要求 .
现在包含整个表格的索引与另一个索引重叠并且变大 . 根据典型查询和 NULL 值的百分比，这可能有用，也可能没用 . 在极端情况下，它甚至可能有助于维护 favo_3col_uni_idx 的两个版本 .

旁白：我建议不要使用mixed case identifiers in PostgreSQL .
回复于 2024-04-28T08:52:07+08:00
48
您可以在MenuId上创建一个带有合并的唯一索引：
```
CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);
```
你可能永远不会在现实生活中看到零UUID但你可以添加一个CHECK约束，如果你是偏执狂（因为它们真的是为了得到你......）：
```
alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')
```
回复于 2024-04-28T08:52:07+08:00

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

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

回复于 2024-04-28T08:52:07+08:00

286
我认为这里存在语义问题 . 在我看来，用户可以拥有（但是 only one ）最喜欢的食谱来准备特定的菜单 . （OP有菜单和配方混合;如果我错了：请在下面交换MenuId和RecipeId）这意味着{user，menu}应该是此表中的唯一键 . 它应该指向 exactly one 食谱 . 如果用户没有此特定菜单的收藏配方，则此{user，menu}密钥对不应存在任何行 . 另外：代理键（FaVouRiteId）是多余的：复合主键对关系映射表完全有效 .

这将导致表格定义减少：
```
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)
);
```
回复于 2024-04-28T08:52:07+08:00

使用空列创建唯一约束

4 回答

相关问题