SQL中如何为Solver表添加复合外键，关联Map表的联合主键？

业务场景说明 #

• 存在开发工作室（Studio），每个工作室开发多款游戏（Game），游戏为工作室独有
• 每款游戏包含多张地图（Map），地图可被不同工作室在不同游戏中重制（即同名地图可属于不同游戏）
• 每张地图对应多个解谜者（Solver）：同一游戏的不同地图允许同名解谜者，不同游戏的同名地图也允许同名解谜者

当前数据库架构 #

CREATE TABLE IF NOT EXISTS Studio 
(
    name TEXT NOT NULL,
    position INT UNIQUE NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    PRIMARY KEY (name)
);

CREATE TABLE IF NOT EXISTS Game 
(
    name TEXT NOT NULL,
    position INT NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    studio TEXT NOT NULL,
    PRIMARY KEY (name),
    FOREIGN KEY (studio) REFERENCES Studio(name)
);

CREATE TABLE IF NOT EXISTS Map 
(
    name TEXT NOT NULL,
    position INT NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    game TEXT NOT NULL,
    PRIMARY KEY (name, game),
    FOREIGN KEY (game) REFERENCES Game(name)
);

CREATE TABLE IF NOT EXISTS Solver 
(
    name TEXT NOT NULL,
    position INT NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    map TEXT NOT NULL,
    game TEXT NOT NULL,
    PRIMARY KEY (name, map, game),
    FOREIGN KEY (map) REFERENCES Map(name),
    FOREIGN KEY (game) REFERENCES Game(name)
);

问题分析 #

当前Solver表的外键是分别关联Map的name字段和Game的name字段，插入记录时仅会检查map值存在于Map表的name列、game值存在于Game表的name列，但无法保证该map与game的组合确实存在于Map表中——而Map表的主键是(name, game)，只有这个组合才能唯一标识一张地图。

解决方案 #

修改Solver表的外键定义，将原来两个单独的外键替换为一个复合外键，直接引用Map表的复合主键(name, game)。这样DBMS会自动校验插入的(map, game)组合是否在Map表中存在，无需触发器就能满足需求，同时还能正常使用CASCADE DELETE/UPDATE功能。

修改后的Solver表创建语句：

CREATE TABLE IF NOT EXISTS Solver 
(
    name TEXT NOT NULL,
    position INT NOT NULL,
    localizedDescription TEXT NOT NULL,
    assetsImageName TEXT NOT NULL,
    map TEXT NOT NULL,
    game TEXT NOT NULL,
    PRIMARY KEY (name, map, game),
    -- 替换原有的两个单独外键为复合外键
    FOREIGN KEY (map, game) REFERENCES Map(name, game),
    -- 与Game表的外键关联为可选：Map表已关联Game，此处可根据需求决定是否保留
    FOREIGN KEY (game) REFERENCES Game(name)
);

补充说明 #

1. 复合外键(map, game)会强制校验插入的Solver记录对应的地图组合在Map表中存在，完全匹配业务需求
2. 保留与Game表的外键属于可选操作：因为Map表已通过外键关联Game，Solver可通过Map间接关联Game，此时去掉该外键可减少冗余校验；若需直接确保Solver的game值合法，也可保留
3. 该方案完全使用SQL标准语法实现，无需自定义触发器，同时支持CASCADE类操作（比如删除某张地图时，可自动删除对应的Solver记录）

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