我有一个这样布局的表格:
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为NULL，这将允许具有相同(UserId, RecipeId)的多行。我想在MenuId中允许NULL来存储没有关联菜单的收藏夹，但每个用户/食谱对最多只需要其中一行。
到目前为止我的想法是:
Use some hard-coded UUID (such as all zeros) instead of null.
However, MenuId has 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.
我使用的是Postgres 9.0。有没有什么方法我忽略了?
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 one recipe. If the user has no favourite recipe for this specific menu no row should exist for this {user,menu} key pair. Also: the surrogate key (FaVouRiteId) is superfluous: composite primary keys are perfectly valid for relational-mapping tables.
这将导致简化表定义:
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)
);
Postgres 15或更新
Postgres 15添加了子句NULLS NOT DISTINCT。发行说明:
允许唯一的约束和索引将NULL值视为不不同的(彼得·艾森特aut)
以前NULL值总是作为不同的值进行索引，但是
现在可以通过使用创建约束和索引来改变这一点
唯一的空值不是唯一的。
在这个子句中，NULL被视为另一个值，UNIQUE约束不允许多行具有相同的NULL值。现在任务很简单:
ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);
在手册的“唯一约束”一章中有一些例子。
子句切换相同索引的所有键的行为。对于一个键，不能将NULL视为相等，而对于另一个键则不能。
NULLS DISTINCT仍然是默认值(与标准SQL一致)，不需要拼写出来。
同样的子句也适用于UNIQUE索引:
CREATE UNIQUE INDEX favo_uni_idx
ON favorites (user_id, menu_id, recipe_id) NULLS NOT DISTINCT;
注意new子句在关键字段之后的位置。
14岁或以上的Postgres
创建两个分部索引:
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为NULL，有效地实现了所需的约束。
可能的缺点:
你不能有外键引用(user_id, menu_id, recipe_id)。(似乎不太可能需要FK引用3列——使用PK列代替!)
群集不能基于部分索引。
没有匹配WHERE条件的查询不能使用部分索引。
如果你需要一个完整的索引，你也可以从favo_3col_uni_idx中删除WHERE条件，你的要求仍然是强制的。
现在包含整个表的索引与另一个索引重叠并变得更大。根据典型的查询和NULL值的百分比，这可能有用，也可能没用。在极端的情况下，它甚至可以帮助保持所有三个指数(两个部分和一个总数在上面)。
对于单个可空列，这是一个很好的解决方案，也许对于两个可空列。但它很快就失控了，因为对于每一个可为空的列的组合，都需要一个单独的部分索引，所以这个数字会以二项增长。对于多个可空列，请参见:
为什么我的唯一约束不触发?
旁白:我建议不要在PostgreSQL中使用混合大小写标识符。
我相信有一个选项可以把前面的答案组合成一个更优的解决方案。
create table unique_with_nulls (
id serial not null,
name varchar not null,
age int2 not null,
email varchar,
email_discriminator varchar not null generated always as ( coalesce(email::varchar, 0::varchar) ) stored,
constraint uwn_pkey primary key (id)
);
create unique index uwn_name_age_email_uidx on unique_with_nulls(name, age, email_discriminator);
What happens here is that the column email_discriminator will be generated at "insert-or-update-time", as either an actual email, or "0" if the former one is null. Then, your unique index must target the discriminator column.
This way we don't have to create two partial indexes, and we don't loose the ability to use indexed scans on name and age selection only.
Also, you can keep the type of the email column and we don't have any problems with the coalesce function, because email_discriminator is not a foreign key. And you don't have to worry about this column receiving unexpected values because generated columns cannot be written to.
我认为这个解决方案有三个缺点，但它们都很适合我的需求:
email和email_discriminator之间的数据复制。
我必须给一个专栏写东西，从另一个专栏读东西。
需要在电子邮件的可接受值集之外找到一个值作为备用值(有时这可能很难找到，甚至是主观的)。
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 one recipe. If the user has no favourite recipe for this specific menu no row should exist for this {user,menu} key pair. Also: the surrogate key (FaVouRiteId) is superfluous: composite primary keys are perfectly valid for relational-mapping tables.
这将导致简化表定义:
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)
);
