PostgreSQL如何限制用户仅存在一个待处理/处理中订单？

要实现你需求的限制，PostgreSQL的**排除约束（EXCLUDE CONSTRAINT）**是最合适的方案，它能直接在数据库层面强制同一用户最多拥有一个status为pending或in_process的订单。

步骤1：确保依赖扩展（如果需要） #

排除约束使用GiST索引时，对于整数类型的user_id，需要先安装btree_gist扩展（PostgreSQL默认不包含）：

CREATE EXTENSION IF NOT EXISTS btree_gist;

步骤2：添加排除约束 #

执行以下SQL语句为orders表添加约束：

ALTER TABLE orders
ADD CONSTRAINT exclude_active_orders_per_user
EXCLUDE USING gist (
    user_id WITH =,
    -- 把pending/in_process标记为同一"活跃"类别
    (CASE WHEN status IN ('pending', 'in_process') THEN 'active' ELSE NULL END) WITH =
)
-- 仅对活跃状态的订单应用约束
WHERE (status IN ('pending', 'in_process'));

约束逻辑说明 #

• 当插入或更新订单时，数据库会检查：如果当前订单的status是pending或in_process，则同一user_id下不能存在其他同样标记为active的订单。
• WHERE子句过滤掉completed和declined状态的订单，这些状态的订单不会触发约束检查，所以同一用户可以有任意多个已完成或被拒绝的订单。
• 你给出的例子中，user_id=1已有一个in_process的订单，此时插入user_id=1且status为pending的订单会直接触发约束错误，完全符合需求。

替代方案：触发器（不推荐） #

虽然可以用触发器实现相同逻辑，但排除约束是数据库原生的约束机制，性能更优且更可靠，不会因为触发器逻辑漏洞导致数据不一致。

内容的提问来源于stack exchange，提问作者Farad