SQL 使用空列创建唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8289100/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create unique constraint with null columns
提问by Mike Christensen
I have a table with this layout:
我有一个这样布局的表：
CREATE TABLE Favorites
(
FavoriteId uuid NOT NULL PRIMARY KEY,
UserId uuid NOT NULL,
RecipeId uuid NOT NULL,
MenuId uuid
)
I want to create a unique constraint similar to this:
我想创建一个与此类似的唯一约束：
ALTER TABLE Favorites
ADD CONSTRAINT Favorites_UniqueFavorite UNIQUE(UserId, MenuId, RecipeId);
However, this will allow multiple rows with the same (UserId, RecipeId), if MenuId IS NULL. I want to allow NULLin MenuIdto store a favorite that has no associated menu, but I only want at most one of these rows per user/recipe pair.
但是，这将允许多行具有相同的(UserId, RecipeId), if MenuId IS NULL。我想允许NULL在MenuId存储不具有关联菜单中的最爱，但我只希望每个用户/食谱对这些行中最多只有一个。
The ideas I have so far are:
到目前为止，我的想法是：
Use some hard-coded UUID (such as all zeros) instead of null.
However,MenuIdhas a FK constraint on each user's menus, so I'd then have to create a special "null" menu for every user which is a hassle.Check for existence of a null entry using a trigger instead.
I think this is a hassle and I like avoiding triggers wherever possible. Plus, I don't trust them to guarantee my data is never in a bad state.Just forget about it and check for the previous existence of a null entry in the middle-ware or in a insert function, and don't have this constraint.
使用一些硬编码的 UUID（例如全零）而不是 null。
但是，MenuId对每个用户的菜单都有 FK 约束，因此我必须为每个用户创建一个特殊的“空”菜单，这很麻烦。使用触发器检查是否存在空条目。
我认为这很麻烦，我喜欢尽可能避免触发。另外，我不相信他们能保证我的数据永远不会处于不良状态。只需忘记它并检查中间件或插入函数中是否存在空条目，并且没有此约束。
I'm using Postgres 9.0.
我正在使用 Postgres 9.0。
Is there any method I'm overlooking?
有没有我忽略的方法？
回答by Erwin Brandstetter
Create 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;
This way, there can only be one combination of (user_id, recipe_id)where menu_id IS NULL, effectively implementing the desired constraint.
这样，只能有(user_id, recipe_id)where 的一种组合menu_id IS NULL，有效地实现所需的约束。
Possible drawbacks: you cannot have a foreign key referencing (user_id, menu_id, recipe_id), you cannot base CLUSTERon a partial index, and queries without a matching WHEREcondition cannot use the partial index. (It seems unlikely you'd want a FK reference three columns wide - use the PK column instead).
可能的缺点：不能有外键引用(user_id, menu_id, recipe_id)，不能CLUSTER基于部分索引，没有匹配WHERE条件的查询不能使用部分索引。（您似乎不太可能想要一个三列宽的 FK 引用 - 改用 PK 列）。
If you need a completeindex, you can alternatively drop the WHEREcondition from favo_3col_uni_idxand your requirements are still enforced.
The index, now comprising the whole table, overlaps with the other one and gets bigger. Depending on typical queries and the percentage of NULLvalues, this may or may not be useful. In extreme situations it might even help to maintain all three indexes (the two partial ones and a total on top).
如果您需要一个完整的索引，您可以选择删除WHERE条件，favo_3col_uni_idx并且您的要求仍然强制执行。
现在包含整个表的索引与另一个重叠并变大。根据典型的查询和NULL值的百分比，这可能有用也可能没有用。在极端情况下，它甚至可能有助于维护所有三个索引（两个部分索引和一个总索引）。
Aside: I advise not to use mixed case identifiers in PostgreSQL.
旁白：我建议不要在 PostgreSQL 中使用大小写混合的标识符。
回答by mu is too short
You could create a unique index with a coalesce on the MenuId:
您可以在 MenuId 上使用合并创建唯一索引：
CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);
You'd just need to pick a UUID for the COALESCE that will never occur in "real life". You'd probably never see a zero UUID in real life but you could add a CHECK constraint if you are paranoid (and since theyreally are out to get you...):
您只需要为“现实生活”中永远不会发生的 COALESCE 选择一个 UUID。在现实生活中，您可能永远不会看到零 UUID，但如果您偏执，您可以添加一个 CHECK 约束（并且因为他们真的想抓住您......）：
alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')
回答by ypercube??
You can store favourites with no associated menu in a separate table:
您可以将没有关联菜单的收藏夹存储在单独的表中：
CREATE TABLE FavoriteWithoutMenu
(
FavoriteWithoutMenuId uuid NOT NULL, --Primary key
UserId uuid NOT NULL,
RecipeId uuid NOT NULL,
UNIQUE KEY (UserId, RecipeId)
)
回答by wildplasser
I think there is a semantic problem here. In my view, a user can have a (but only one) favourite recipe to prepare a specific menu. (The OP has menu and recipe mixed up; if I am wrong: please interchange MenuId and RecipeId below) That implies that {user,menu} should be a unique key in this table. And it should point to exactly onerecipe. If the user has no favourite recipe for this specific menu no row should existfor this {user,menu} key pair. Also: the surrogate key (FaVouRiteId) is superfluous: composite primary keys are perfectly valid for relational-mapping tables.
我认为这里存在语义问题。在我看来，用户可以有一个（但只有一个）最喜欢的食谱来准备特定的菜单。（OP 混淆了菜单和食谱；如果我错了：请交换下面的 MenuId 和 RecipeId）这意味着 {user,menu} 应该是此表中的唯一键。它应该指向一个食谱。如果用户对此特定菜单没有最喜欢的食谱，则此 {user,menu} 密钥对不应存在任何行。另外：代理键 (FaVouRiteId) 是多余的：复合主键对于关系映射表完全有效。
That would lead to the reduced table definition:
这将导致表定义减少：
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)
);
