PostgreSQL不允许有多行满足条件

nfzehxib  于 2023-04-05  发布在  PostgreSQL
关注(0)|答案(3)|浏览(132)

我有一个表orders,列是:iduser_idstatusstatus列是枚举,其值为:pending、in_process、completed、decided。
我需要禁止一个用户有一个以上的订单与status in (pending, in_process)
例如,具有表:
| 身份证|地位|用户标识|
| --------------|--------------|--------------|
| 1|已完成|1|
| 第二章|正在处理|1|
| 三|待定|第二章|
对于user_id=1,我们不能再插入一个状态为pendingin_process的订单。
我觉得我应该在这里使用一个排除约束,但不知道如何禁止两个集合的交集。

ibrsph3r

ibrsph3r1#

这里你不是在寻找一个排除约束(因为你想允许任何数量的completed行,它们都具有相同的user_idstatus值),而是一个conditionally unique constraint-postgres不支持CONSTRAINT,但你可以使用partial UNIQUE index

CREATE UNIQUE INDEX unique_active_order
ON orders (user_id)
WHERE (status IN ('pending', 'in_process'));

CREATE UNIQUE INDEX unique_active_order
ON orders (user_id)
WHERE (status NOT IN ('completed','declined'));
lf3rwulv

lf3rwulv2#

你要找的是一个partial unique index。因为status是一个枚举,所以你很可能必须对数组进行类型转换。

create unique index on orders (user_id, status)
where status = any ( array['pending', 'in_process']::status_enum[] );
xdyibdwo

xdyibdwo3#

BelayerBergi击败我张贴部分独特的方法,所以对于体育:您 * 可以 * 使排除约束对此起作用。demo

create type status_enum as enum('pending','in_process','completed','declined');

create table orders (
   id smallserial primary key,
   user_id int,
   status status_enum default 'pending',
   exclude (user_id with =) where (status in ('pending','in_process') ) 
);

insert into orders (status,user_id) values
('completed', 1),
('in_process',1),
('pending',   2);
--INSERT 0 3
insert into orders (user_id,status) values (1,'pending');
--ERROR:  conflicting key value violates exclusion constraint "orders_user_id_excl"
--DETAIL:  Key (user_id)=(1) conflicts with existing key (user_id)=(1).
insert into orders (user_id,status) values (1,'in_process');
--ERROR:  conflicting key value violates exclusion constraint "orders_user_id_excl"
--DETAIL:  Key (user_id)=(1) conflicts with existing key (user_id)=(1).
insert into orders (user_id,status) values (1,'declined');--no problem
--INSERT 0 1

在这些where status in ('pending','in_process')中,没有两个user_id可以是相同的。
我说这是 * 为体育 *,因为它可以工作,但没有提供超过partial unique的好处:
尽管允许使用B树或哈希索引,但使用带有排除约束的索引没有什么意义,因为这并没有比普通的唯一约束做得更好的事情。

相关问题