PostgreSQL如何为users表实现满足多条件的check约束

直接执行以下SQL语句即可添加符合要求的约束：

ALTER TABLE users
ADD CONSTRAINT users_merchant_agent_check 
CHECK (
  (type = 'MERCHANT' AND merchant_id IS NOT NULL AND agent_id IS NULL)
  OR
  (type = 'AGENT' AND agent_id IS NOT NULL AND merchant_id IS NULL)
);

如果你需要额外限制type字段仅能取MERCHANT、AGENT两个枚举值，可以调整约束逻辑如下：

ALTER TABLE users
ADD CONSTRAINT users_merchant_agent_check 
CHECK (
  type IN ('MERCHANT', 'AGENT')
  AND (
    (type = 'MERCHANT' AND merchant_id IS NOT NULL AND agent_id IS NULL)
    OR
    (type = 'AGENT' AND agent_id IS NOT NULL AND merchant_id IS NULL)
  )
);

约束逻辑说明 #

• 当type取值为MERCHANT时，强制要求merchant_id非空，同时agent_id必须为空，符合单用户只能持有一种身份ID的要求
• 当type取值为AGENT时，强制要求agent_id非空，同时merchant_id必须为空
• 两个分支逻辑互斥，天然避免了merchant_id和agent_id同时有值的情况

注意事项 #

如果表中已有不符合约束规则的旧数据，执行上述语句会报错，你可以先执行以下查询定位不符合要求的存量数据，清理完成后再添加约束：

SELECT * FROM users
WHERE 
  (type = 'MERCHANT' AND (merchant_id IS NULL OR agent_id IS NOT NULL))
  OR
  (type = 'AGENT' AND (agent_id IS NULL OR merchant_id IS NOT NULL));

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