如何为PostgreSQL子表设置数据完整性约束？

针对你的两个数据完整性约束，我们可以结合CHECK约束和触发器函数来实现，具体如下：

1. 约束my_table_b.my_field_b必须大于0 #

这个约束可以直接用PostgreSQL内置的CHECK约束实现，无需触发器，效率更高：

ALTER TABLE my_table_b
ADD CONSTRAINT chk_my_field_b_positive CHECK (my_field_b > 0);

后续插入或更新my_table_b时，若my_field_b的值≤0，数据库会直接抛出错误并阻止操作。

2. 仅当父表my_table_a对应记录的my_field_a为True时，允许操作my_table_b #

这个约束需要从两个维度做校验：

• 对my_table_b的插入/更新操作做前置校验
• 对my_table_a的更新操作做校验（防止父表记录的my_field_a从True改为False时，仍存在关联的子表记录）

第一步：创建触发器函数 #

-- 校验my_table_b操作时父表my_field_a状态的函数
CREATE OR REPLACE FUNCTION validate_my_table_b_parent()
RETURNS TRIGGER AS $$
BEGIN
  -- 检查父表对应记录的my_field_a是否为True
  IF NOT EXISTS (
    SELECT 1 FROM my_table_a 
    WHERE id = NEW.fk_id AND my_field_a = TRUE
  ) THEN
    RAISE EXCEPTION '仅当my_table_a对应记录的my_field_a为True时，才能操作my_table_b的记录';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 校验my_table_a更新时是否存在关联子表记录的函数
CREATE OR REPLACE FUNCTION validate_my_table_a_update()
RETURNS TRIGGER AS $$
BEGIN
  -- 若将my_field_a从True改为False，检查是否有子表关联记录
  IF OLD.my_field_a = TRUE AND NEW.my_field_a = FALSE THEN
    IF EXISTS (
      SELECT 1 FROM my_table_b 
      WHERE fk_id = NEW.id
    ) THEN
      RAISE EXCEPTION '无法将my_table_a的my_field_a改为False，因为存在关联的my_table_b记录';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

第二步：绑定触发器到对应表 #

-- 给my_table_b添加插入、更新触发器
CREATE TRIGGER trg_my_table_b_insert
BEFORE INSERT ON my_table_b
FOR EACH ROW EXECUTE FUNCTION validate_my_table_b_parent();

CREATE TRIGGER trg_my_table_b_update
BEFORE UPDATE ON my_table_b
FOR EACH ROW EXECUTE FUNCTION validate_my_table_b_parent();

-- 给my_table_a添加更新触发器（仅监听my_field_a字段的变更）
CREATE TRIGGER trg_my_table_a_update
BEFORE UPDATE OF my_field_a ON my_table_a
FOR EACH ROW EXECUTE FUNCTION validate_my_table_a_update();

补充说明 #

• CHECK约束是数据库层面的轻量级校验，性能优于触发器，适合简单的字段范围限制
• 触发器函数会在操作执行前完成校验，不符合规则时抛出明确错误，直接阻断非法操作
• my_table_a的更新触发器确保了数据完整性的闭环，避免出现父表状态变更后子表数据违反约束的情况

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