数据库设计：两表同引第三表时如何保证三者关联关系一致性？

共有两种常用的实现方案，优先推荐使用复合外键的方案，可靠性更高、维护成本更低。

方案1：复合外键约束（推荐） #

利用外键的一致性校验能力，仅需两步配置即可实现全自动的一致性保证：

1. 先给agents表添加agent_id + company_id的复合唯一约束（因为agent_id本身是主键，该约束不会产生额外的重复校验成本）
2. 给customers表创建复合外键，将(agent_id, company_id)关联到agents表的复合唯一键

示例SQL（MySQL兼容） #

-- 给agents表添加复合唯一键
ALTER TABLE agents ADD UNIQUE KEY uk_agent_company (agent_id, company_id);

-- 给customers表添加复合外键约束
ALTER TABLE customers 
ADD CONSTRAINT fk_customer_agent_company 
FOREIGN KEY (agent_id, company_id) 
REFERENCES agents (agent_id, company_id)
ON UPDATE CASCADE; -- 可选配置：如果agent所属公司变更，自动同步customer的company_id

该方案的优势：

• 仅当agent_id非空时才会触发外键校验，完全符合「未分配agent/公司无agent时自行存储company_id」的业务规则
• 所有一致性校验由数据库引擎原生实现，性能远高于自定义逻辑，不会出现逻辑漏洞
• 支持级联更新，不需要额外开发同步逻辑

方案2：触发器校验（兼容不支持复合外键的旧版数据库） #

如果你的数据库不支持复合外键，可以通过行级触发器实现校验逻辑，在customers表执行INSERT/UPDATE操作前校验数据一致性。

示例SQL（PostgreSQL兼容） #

-- 创建校验函数
CREATE OR REPLACE FUNCTION check_customer_company_match()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.agent_id IS NOT NULL THEN
    IF NEW.company_id != (SELECT company_id FROM agents WHERE agent_id = NEW.agent_id) THEN
      RAISE EXCEPTION '客户所属公司与对接Agent所属公司不匹配';
    END IF;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到customers表
CREATE TRIGGER trigger_customer_company_check
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION check_customer_company_match();

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