MySQL如何通过父表使用祖父表键约束孙表 无需触发器实现数据校验

方案一：复合外键实现（全数据库兼容，推荐） #

该方案是SQL标准兼容的通用实现，所有支持外键的主流数据库（MySQL、PostgreSQL、SQL Server等）都可以使用。

• 第一步：给users和locations表新增联合唯一约束，将organization_id包含进唯一键中。由于user_id、location_id本身是对应表的主键，该约束不会产生冗余冲突：

ALTER TABLE users ADD CONSTRAINT uq_users_id_org UNIQUE (user_id, organization_id);
ALTER TABLE locations ADD CONSTRAINT uq_locations_id_org UNIQUE (location_id, organization_id);

• 第二步：给user_locations表新增organization_id字段，同时创建两个指向上述联合唯一键的复合外键：

-- 字段类型需和其他表的organization_id保持一致
ALTER TABLE user_locations ADD COLUMN organization_id INT NOT NULL;
-- 关联用户表的联合唯一键，保证用户和组织匹配
ALTER TABLE user_locations ADD CONSTRAINT fk_ul_user_org FOREIGN KEY (user_id, organization_id) REFERENCES users(user_id, organization_id);
-- 关联地址表的联合唯一键，保证地址和组织匹配
ALTER TABLE user_locations ADD CONSTRAINT fk_ul_location_org FOREIGN KEY (location_id, organization_id) REFERENCES locations(location_id, organization_id);

实现原理：两个外键会强制user_locations中的organization_id必须同时匹配对应用户的所属组织、对应地址的所属组织，自然就杜绝了跨组织分配地址的可能。如果不想显式维护user_locations的organization_id字段，在支持计算列/生成列的数据库中，可以把该字段设为自动生成的计算列，进一步减少业务代码的修改量。

方案二：CHECK约束实现（仅部分数据库支持） #

如果你的数据库支持在CHECK约束中写标量子查询（比如PostgreSQL），也可以直接加CHECK约束，不需要修改其他表结构：

ALTER TABLE user_locations ADD CONSTRAINT chk_same_org CHECK (
  (SELECT organization_id FROM users WHERE user_id = user_locations.user_id) 
  = 
  (SELECT organization_id FROM locations WHERE location_id = user_locations.location_id)
);

注意：该方案兼容性较差，MySQL、SQL Server等数据库不支持CHECK约束中包含子查询，且部分场景下校验性能不如复合外键方案。

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