PostgreSQL如何依据另一张表的字段值为指定列添加非空约束

你需要的跨表关联条件校验有3种常用的可行实现方式：

方案1：触发器实现（最通用，无场景限制） #

这是最稳妥的实现方式，需要分别覆盖两张表的变更场景，避免数据不一致：

步骤1：创建Value表插入/更新校验函数 #

CREATE OR REPLACE FUNCTION check_value_required()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM Ref 
    WHERE id = NEW.refId AND required = TRUE
  ) AND NEW.value IS NULL THEN
    RAISE EXCEPTION '关联Ref记录要求value字段非空，refId: %', NEW.refId;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：创建Value表触发器 #

CREATE TRIGGER trigger_value_required_check
BEFORE INSERT OR UPDATE OF refId, value ON Value
FOR EACH ROW EXECUTE FUNCTION check_value_required();

步骤3：创建Ref表更新校验函数（防止Ref修改required后数据不一致） #

CREATE OR REPLACE FUNCTION check_ref_required_update()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.required = TRUE AND EXISTS (
    SELECT 1 FROM Value 
    WHERE refId = NEW.id AND value IS NULL
  ) THEN
    RAISE EXCEPTION '存在关联的Value记录value为空，无法将refId: %的required设为TRUE', NEW.id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤4：创建Ref表触发器 #

CREATE TRIGGER trigger_ref_required_check
BEFORE UPDATE OF required ON Ref
FOR EACH ROW EXECUTE FUNCTION check_ref_required_update();

优缺点：所有变更场景都能覆盖，兼容性好；需要额外维护触发器逻辑，高并发场景下性能略低于声明式约束

方案2：CHECK约束调用跨表查询函数（实现简单，仅适合Ref.required不变的场景） #

如果你的业务中Ref表的required字段一旦写入就不会修改，可以用这个简化方案：

步骤1：创建校验函数 #

CREATE OR REPLACE FUNCTION is_ref_required(ref_id INTEGER)
RETURNS BOOLEAN AS $$
  SELECT required FROM Ref WHERE id = ref_id;
$$ LANGUAGE sql STABLE;

步骤2：给Value表加CHECK约束 #

ALTER TABLE Value ADD CONSTRAINT check_value_required
CHECK (is_ref_required(refId) = FALSE OR value IS NOT NULL);

优缺点：代码简洁，声明式约束易维护；仅Value表变更时会触发校验，Ref表修改required字段不会触发校验，容易出现数据不一致

方案3：冗余字段+联合外键约束（声明式约束，无触发器） #

通过冗余字段把跨表约束转为同表约束，完全基于原生约束实现，不需要写自定义逻辑：

步骤1：给Ref表加联合唯一键 #

ALTER TABLE Ref ADD CONSTRAINT uniq_ref_id_required UNIQUE (id, required);

步骤2：给Value表加冗余字段和联合外键 #

ALTER TABLE Value ADD COLUMN ref_required BOOLEAN NOT NULL;
ALTER TABLE Value ADD CONSTRAINT fk_value_ref_required
FOREIGN KEY (refId, ref_required) REFERENCES Ref(id, required);

步骤3：给Value表加同表CHECK约束 #

ALTER TABLE Value ADD CONSTRAINT check_value_required
CHECK (ref_required = FALSE OR value IS NOT NULL);

优缺点：完全使用原生声明式约束，逻辑可靠，性能最好；需要冗余存储ref_required字段，插入Value记录时需要额外查询Ref的required值填入冗余字段

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