用空列创建唯一约束

我有一个这样布局的表格:

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存储一个没有关联菜单的收藏夹，但每个用户/食谱对最多只需要其中一行。

到目前为止我的想法是:

  1. 使用一些硬编码的UUID(例如全零)而不是null。< br / > 然而，MenuId对每个用户的菜单都有一个FK约束，所以我必须创建一个特殊的&;null&;菜单为每个用户，这是一个麻烦

  2. 使用触发器检查是否存在空条目。< br / > 我认为这是一个麻烦，我喜欢尽可能避免触发。另外，我不相信他们能保证我的数据永远不会处于糟糕的状态

  3. 忽略它，检查之前在中间件或插入函数中是否存在null项，不要有这个约束。

我使用的是Postgres 9.0。有没有什么方法我忽略了?

150560 次浏览
小开

你可以在一个单独的表中存储没有相关菜单的收藏夹:

CREATE TABLE FavoriteWithoutMenu
(
FavoriteWithoutMenuId uuid NOT NULL, --Primary key
UserId uuid NOT NULL,
RecipeId uuid NOT NULL,
UNIQUE KEY (UserId, RecipeId)
)
小开
最佳答案

Postgres 15或更新

Postgres 15添加了NULLS NOT DISTINCT子句。发行说明:

  • 允许唯一的约束和索引将NULL值视为不不同的(Peter Eisentraut)

    以前NULL值总是被索引为不同的值，但是 现在可以通过使用创建约束和索引来改变这一点 UNIQUE NULLS NOT DISTINCT . < / p >

在此子句中，NULL被视为另一个值，并且UNIQUE约束不允许具有相同NULL值的多行。现在任务很简单:

ALTER TABLE favorites
ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id);
手册中的< em >“独特Constraints" < / em >章节中有一些例子。
子句切换相同索引的所有键的行为。你不能对一个键把NULL当作相等的，而对另一个键却不是。
NULLS DISTINCT仍然是默认值(与标准SQL一致)，不需要拼写出来

同样的子句也适用于UNIQUE指数:

CREATE UNIQUE INDEX favo_uni_idx
ON favorites (user_id, menu_id, recipe_id) NULLS NOT DISTINCT;

注意关键字段的新子句后的位置。

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 IS NULL只能有一个组合，有效地实现了所需的约束。

可能的缺点:

  • 不能有引用(user_id, menu_id, recipe_id)的外键。(似乎不太可能需要FK引用3列——使用PK列代替!)
  • CLUSTER不能基于部分索引。
  • 没有匹配WHERE条件的查询不能使用部分索引。
如果你需要一个完整的索引，你也可以从favo_3col_uni_idx中删除WHERE条件，你的要求仍然是强制的。
现在包含整个表的索引与另一个索引重叠并变得更大。根据典型的查询和NULL值的百分比，这可能有用，也可能没用。在极端情况下，它甚至可以帮助保持所有三个索引(两个部分索引和一个总的索引在上面)

这对于单个可空列是一个很好的解决方案，也许对于两个。但它很快就失控了，因为对于每一个可为空的列的组合，都需要一个单独的部分索引，所以这个数字会以二项增长。对于多个可空列，请参见:

旁白:我建议不要使用PostgreSQL中的混合大小写标识符。

小开

你可以在菜单id上创建一个唯一的索引:

CREATE UNIQUE INDEX
Favorites_UniqueFavorite ON Favorites
(UserId, COALESCE(MenuId, '00000000-0000-0000-0000-000000000000'), RecipeId);

您只需要为COALESCE选择一个在“现实生活”中永远不会出现的UUID。你可能永远不会在现实生活中看到一个0 UUID，但如果你是偏执的，你可以添加一个CHECK约束(因为他们真的是为了得到你…):

alter table Favorites
add constraint check
(MenuId <> '00000000-0000-0000-0000-000000000000')
小开
我认为这里有一个语义问题。在我看来，用户可以有一个(但是只有一个)最喜欢的食谱来准备一个特定的菜单。(OP的菜单和食谱混在一起了;如果我错了:请在下面交换MenuId和RecipeId) 这意味着{user,menu}应该是该表中的唯一键。它应该指向一个 recipe。如果对于这个{user,menu}键对，用户对于这个特定的菜单不应该存在行没有最喜欢的食谱。另外:代理键(favoriteid)是多余的:复合主键对于关系映射表是完全有效的

这将导致简化表定义:

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)
);
小开

我相信有一个选项可以把前面的答案组合成一个更优的解决方案。

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);
这里发生的事情是，列email_discriminator将在“插入或更新时间”生成，作为一个实际的电子邮件，或者“;0"如果前者为空。然后，唯一索引必须以鉴别器列为目标。
这样我们就不必创建两个部分索引，而且我们也不会失去仅在name和age选择上使用索引扫描的能力。
另外，你可以保留email列的类型，并且使用coalesce函数不会有任何问题，因为email_discriminator不是外键。并且您不必担心这个列会接收到意外的值，因为生成的列不能被写入

我认为这个解决方案有三个缺点，但它们都很适合我的需求:

  • email和email_discriminator之间的数据复制。
  • 我必须给一个专栏写东西，从另一个专栏读东西。
  • 需要在email的可接受值集之外找到一个值作为备用值(有时这可能很难找到，甚至是主观的)。

提交答案