SQL最佳实践：如何用同一外键关联两个不同表的主键？

针对你遇到的「一个字段关联两个不同表主键」的场景，结合你的司法机构层级业务逻辑，有几种成熟的数据库设计方案：

1. 建立父表实现继承（最符合范式的推荐方案） #

既然上诉法院和法庭同属司法机构且存在层级关系，最合理的做法是先创建统一的父表judicial_bodies，存放两者的共同属性，再让court_of_appeal和tribunal作为子表存储独有字段：

父表结构 #

CREATE TABLE judicial_bodies (
    id VARCHAR(10) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) NOT NULL CHECK (type IN ('COURT', 'TRIBUNAL')) -- 标记机构类型
);

子表结构 #

-- 上诉法院表（无额外字段，直接关联父表主键）
CREATE TABLE court_of_appeal (
    id VARCHAR(10) PRIMARY KEY REFERENCES judicial_bodies(id)
);

-- 法庭表，新增关联上诉法院的字段
CREATE TABLE tribunal (
    id VARCHAR(10) PRIMARY KEY REFERENCES judicial_bodies(id),
    related_court_of_appeal_id VARCHAR(10) NOT NULL REFERENCES court_of_appeal(id)
);

Auction表关联父表 #

CREATE TABLE auction (
    tribunal_or_court_id VARCHAR(10) NOT NULL REFERENCES judicial_bodies(id),
    auction_id VARCHAR(10) NOT NULL,
    description VARCHAR(100),
    PRIMARY KEY (tribunal_or_court_id, auction_id)
);

这个方案的优势：

• 完全符合数据库范式，避免数据冗余
• 天然支持后续扩展其他司法机构类型
• 查询时可通过父表统一获取所有机构信息，关联逻辑更简洁

2. 在Auction表添加类型字段+约束（轻量改造方案） #

如果不想大幅调整现有表结构，可以给Auction表新增body_type字段标记机构类型，再通过约束保证数据一致性：

修改Auction表结构 #

ALTER TABLE auction ADD COLUMN body_type VARCHAR(20) NOT NULL CHECK (body_type IN ('COURT', 'TRIBUNAL'));

-- 更新主键：加入类型字段，避免同一拍卖ID对应不同类型机构的冲突
ALTER TABLE auction DROP CONSTRAINT auction_pkey;
ALTER TABLE auction ADD PRIMARY KEY (tribunal_or_court_id, auction_id, body_type);

添加部分外键（以PostgreSQL为例） #

-- 当类型为COURT时，校验ID存在于上诉法院表
ALTER TABLE auction ADD CONSTRAINT chk_court_valid CHECK (
    (body_type = 'COURT' AND EXISTS (SELECT 1 FROM court_of_appeal WHERE id = tribunal_or_court_id))
    OR body_type != 'COURT'
);
ALTER TABLE auction ADD CONSTRAINT fk_auction_court FOREIGN KEY (tribunal_or_court_id) REFERENCES court_of_appeal(id)
    DEFERRABLE INITIALLY DEFERRED;

-- 当类型为TRIBUNAL时，校验ID存在于法庭表
ALTER TABLE auction ADD CONSTRAINT chk_tribunal_valid CHECK (
    (body_type = 'TRIBUNAL' AND EXISTS (SELECT 1 FROM tribunal WHERE id = tribunal_or_court_id))
    OR body_type != 'TRIBUNAL'
);
ALTER TABLE auction ADD CONSTRAINT fk_auction_tribunal FOREIGN KEY (tribunal_or_court_id) REFERENCES tribunal(id)
    DEFERRABLE INITIALLY DEFERRED;

如果使用MySQL（不支持部分外键），可以用触发器替代上述校验逻辑，在插入/更新Auction记录时检查对应的ID是否存在于指定表中。

3. 联合视图+触发器（兼容现有结构的折中方案） #

创建包含上诉法院和法庭所有ID的联合视图，再通过触发器维护Auction表的数据一致性：

创建联合视图 #

CREATE VIEW all_judicial_bodies AS
    SELECT id, 'COURT' AS type FROM court_of_appeal
    UNION ALL
    SELECT id, 'TRIBUNAL' AS type FROM tribunal;

然后编写触发器，在Auction表插入/更新时，校验tribunal_or_court_id是否存在于上述视图中。这种方案无需修改现有表结构，但维护成本较高，仅作为临时折中选项。

方案选择建议 #

优先选择方案1，它完美匹配你业务中的司法机构层级关系，后续维护和扩展性最优；若无法大幅调整现有结构，则选方案2，通过类型字段+约束实现轻量改造，保证数据一致性。

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