如何创建双向外键关联表的SQL查询？该方案是否合理？

嘿，我来帮你拆解这个问题～首先咱们先聊聊这种双向外键关联的设计是否可行，再给出对应的SQL查询写法。

一、这种双向关联设计的可行性 #

这种X(a)PK ↔ Y(a)FK + X(b)FK ↔ Y(b)PK的双向循环关联设计是可行的，但有几个关键注意点需要留意：

• 数据插入/更新顺序：因为两张表互相依赖外键约束，直接同时插入会触发约束错误。你需要分步操作：先插入其中一张表的记录（暂时把依赖对方的外键设为NULL），再插入另一张表的关联记录，最后回更更新第一张表的外键字段。
• 约束设置：要确保外键字段允许NULL（除非你能保证插入顺序完全匹配，但实际场景里很难做到），否则根本无法初始化数据。
• 维护复杂度：这种设计会增加数据维护的难度，比如删除记录时，必须先解除双向的外键关联（比如把其中一方的外键设为NULL），才能删除对应的记录，否则会触发约束报错。
• 适用场景：这种设计一般用于两张表是强关联的一对一关系，比如用户表和用户核心详情表的双向绑定，但更常见的是用单表或者单向外键+唯一约束来实现，双向关联更多是特殊业务需求下的选择。

二、对应的SQL查询写法 #

假设你的表结构如下（简化版）：

• 表X：a为主键，b为外键关联Y的b字段
• 表Y：b为主键，a为外键关联X的a字段

1. 关联查询两张表的匹配数据 #

如果要查询同时满足X.a=Y.a和X.b=Y.b的关联数据，可以用INNER JOIN：

SELECT X.*, Y.*
FROM X
INNER JOIN Y 
  ON X.a = Y.a 
  AND X.b = Y.b;

2. 查询X的所有记录（含无匹配的Y） #

如果想保留X的所有记录，即使没有对应的Y数据，用LEFT JOIN：

SELECT X.*, Y.*
FROM X
LEFT JOIN Y 
  ON X.a = Y.a 
  AND X.b = Y.b;

3. 初始化数据的示例（避免约束错误） #

刚才提到的分步插入操作示例：

-- 第一步：插入X的记录，b暂时设为NULL
INSERT INTO X (a, b) VALUES (1, NULL);

-- 第二步：插入Y的关联记录，a用X的主键值
INSERT INTO Y (a, b) VALUES (1, 2);

-- 第三步：更新X的b字段为Y的主键值，完成双向关联
UPDATE X SET b = 2 WHERE a = 1;

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