PostgreSQL双向不同外键的匹配校验实现问询

你描述的这种双向关联场景（product表通过preferred_vendor_product关联vendor_product，vendor_product通过product_id关联product），PostgreSQL的基础外键只能保证preferred_vendor_product指向存在的vendor_product记录，但无法直接校验该vendor_product的product_id是否与当前操作的product的id一致。要实现这个校验逻辑，有两种靠谱的方案：

方案一：触发器+自定义函数 #

通过触发器在插入或更新product记录时，自动校验关联的vendor_product归属关系：

1. 创建校验函数

CREATE OR REPLACE FUNCTION check_preferred_vendor_match()
RETURNS TRIGGER AS $$
BEGIN
  -- 若设置了首选供应商产品，校验其是否属于当前商品
  IF NEW.preferred_vendor_product IS NOT NULL THEN
    PERFORM 1 FROM vendor_product
    WHERE id = NEW.preferred_vendor_product AND product_id = NEW.id;
    IF NOT FOUND THEN
      RAISE EXCEPTION '所选的首选供应商产品不属于当前商品';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到product表

CREATE TRIGGER trigger_check_preferred_vendor
BEFORE INSERT OR UPDATE ON product
FOR EACH ROW EXECUTE FUNCTION check_preferred_vendor_match();

之后每次插入或更新product的preferred_vendor_product字段时，数据库都会自动执行校验，不满足条件就抛出错误终止操作。

方案二：复合外键+唯一约束（更简洁） #

利用复合外键的特性，直接通过数据库约束实现双重校验：

1. 给vendor_product表添加复合唯一约束

ALTER TABLE vendor_product ADD CONSTRAINT uq_vendor_product_product_id_id UNIQUE (product_id, id);

（注：因为id本身是主键已经唯一，这个约束是冗余的，但它是创建复合外键的必要前提）

2. 修改product表，创建复合外键

ALTER TABLE product
ADD CONSTRAINT fk_product_preferred_vendor
FOREIGN KEY (id, preferred_vendor_product)
REFERENCES vendor_product (product_id, id);

这个方案让product的(id, preferred_vendor_product)必须匹配vendor_product中的(product_id, id)记录，同时满足两个要求：preferred_vendor_product存在，且对应的vendor_product属于当前product。

方案对比 #

• 触发器方案更灵活，适合后续需要扩展复杂校验逻辑的场景；
• 复合外键方案更简洁，属于原生数据库约束，性能更优，维护成本更低。

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