PostgreSQL timesheets表多规则校验CHECK约束实现

你当前编写的CHECK表达式存在两处核心逻辑偏差，和实际业务规则不匹配：

• 错误拦截合法业务场景
  现有表达式仅保留两类允许的入库分支：要么仅task_schedule_id非空、其余三个关联字段全空；要么user_id非空、至少关联一个location_id/customer_id、且task_schedule_id为空。这会直接拦截两类实际业务中合法的记录：
  • 四个关联字段全为空的全局通用创建工时
  • 仅user_id非空、location_id/customer_id/task_schedule_id全为空的、仅关联员工的通用工时
• 逻辑耦合度过高
  所有规则被揉进一个长OR表达式里，虽然碰巧覆盖了大部分非法组合，但没有独立对应每一条业务规则，后续调整规则时很容易出现边界漏判，且一旦触发校验失败，数据库返回的报错无法直接定位是哪条规则没有满足，排查成本高。

不建议把所有规则揉在一个长表达式里，推荐将每条业务规则拆为独立的CHECK约束，可读性、可维护性都更高，每段逻辑严格对应提出的校验要求：

-- 规则1：只要user_id已赋值，task_schedule_id必须为NULL
ALTER TABLE timesheets
ADD CONSTRAINT chk_user_exclude_task_schedule
CHECK (user_id IS NULL OR task_schedule_id IS NULL);

-- 规则3：只要task_schedule_id已赋值，user_id/customer_id/location_id必须全为NULL
ALTER TABLE timesheets
ADD CONSTRAINT chk_task_schedule_exclude_other_fks
CHECK (
  NOT (
    task_schedule_id IS NOT NULL 
    AND (user_id IS NOT NULL OR customer_id IS NOT NULL OR location_id IS NOT NULL)
  )
);

-- 规则2-1：location_id或customer_id任意一个已赋值时，必须满足user_id非空
ALTER TABLE timesheets
ADD CONSTRAINT chk_customer_location_require_user
CHECK (
  NOT (
    (location_id IS NOT NULL OR customer_id IS NOT NULL)
    AND user_id IS NULL
  )
);

-- 规则2-2：不允许location_id与customer_id同时赋值
ALTER TABLE timesheets
ADD CONSTRAINT chk_no_duplicate_customer_location
CHECK (
  NOT (location_id IS NOT NULL AND customer_id IS NOT NULL)
);

这类拆分后的约束，会完全覆盖所有合法场景，也不会拦截旧代码误拦的两类通用工时记录，同时所有非法组合都会被精准拦截：

• 全空的四个关联字段：所有约束的触发条件都不满足，允许入库
• 仅user_id非空、其余关联字段全空：所有约束都满足，允许入库
• task_schedule_id非空、其余关联字段全空：满足关联排期的校验要求，允许入库
• user_id非空+任意单个location_id/customer_id、其余关联字段为空：满足所有绑定规则，允许入库

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