如何在SQLite中让外键关联多个表/字段？

需求说明 #

Monster表的Origin字段当前是关联Country表Name字段的外键，需要修改为可关联Country表或新增的Planet表的Name字段，要求：

• 不添加中间表
• 不重命名表
• 不为Monster或其他表新增字段
• 尝试仅通过修改外键实现（此前曾用带区分字段的虚拟列解决类似问题，但本次Monster表无对应区分字段）

初始及新增表结构 #

初始表结构 #

CREATE TABLE Monster (
  Id INTEGER PRIMARY KEY,
  Name VARCHAR NOT NULL UNIQUE,
  Origin TEXT,
  FOREIGN KEY (Origin) REFERENCES Country(Name) ON UPDATE CASCADE
);

CREATE TABLE Country (
   Id INTEGER PRIMARY KEY,
   Name VARCHAR NOT NULL UNIQUE
);

新增Planet表 #

CREATE TABLE Planet (
   Id INTEGER PRIMARY KEY,
   Name VARCHAR NOT NULL UNIQUE
);

方案说明 #

SQLite原生外键机制不支持单个字段同时关联多个表，外键只能指向单个真实表的特定列。在你提出的约束条件下，无法仅通过修改外键实现需求。若要保证Origin字段的值必须存在于Country或Planet表中，可通过触发器替代外键的完整性检查，具体实现如下：

1. 移除原外键约束 #

SQLite不支持直接修改现有表的外键约束，需重新创建表并迁移数据：

-- 备份Monster表数据
CREATE TABLE Monster_temp AS SELECT * FROM Monster;

-- 删除原表
DROP TABLE Monster;

-- 重新创建无外键的Monster表
CREATE TABLE Monster (
  Id INTEGER PRIMARY KEY,
  Name VARCHAR NOT NULL UNIQUE,
  Origin TEXT
);

-- 恢复数据
INSERT INTO Monster SELECT * FROM Monster_temp;

-- 删除临时表
DROP TABLE Monster_temp;

2. 创建触发器实现多表存在性检查 #

创建插入和更新触发器，确保Origin的值在Country或Planet表中存在：

-- 插入数据前检查
CREATE TRIGGER monster_origin_check_insert
BEFORE INSERT ON Monster
FOR EACH ROW
WHEN NOT EXISTS (SELECT 1 FROM Country WHERE Name = NEW.Origin)
AND NOT EXISTS (SELECT 1 FROM Planet WHERE Name = NEW.Origin)
BEGIN
  SELECT RAISE(ABORT, 'Origin必须存在于Country或Planet表中');
END;

-- 更新数据前检查
CREATE TRIGGER monster_origin_check_update
BEFORE UPDATE ON Monster
FOR EACH ROW
WHEN NOT EXISTS (SELECT 1 FROM Country WHERE Name = NEW.Origin)
AND NOT EXISTS (SELECT 1 FROM Planet WHERE Name = NEW.Origin)
BEGIN
  SELECT RAISE(ABORT, 'Origin必须存在于Country或Planet表中');
END;

3. 可选：实现级联更新 #

若需保留原外键的ON UPDATE CASCADE功能，为Country和Planet表添加更新触发器：

-- Country表Name更新时同步修改Monster的Origin
CREATE TRIGGER country_name_update_cascade
AFTER UPDATE OF Name ON Country
FOR EACH ROW
BEGIN
  UPDATE Monster SET Origin = NEW.Name WHERE Origin = OLD.Name;
END;

-- Planet表Name更新时同步修改Monster的Origin
CREATE TRIGGER planet_name_update_cascade
AFTER UPDATE OF Name ON Planet
FOR EACH ROW
BEGIN
  UPDATE Monster SET Origin = NEW.Name WHERE Origin = OLD.Name;
END;

补充说明 #

你之前使用的虚拟列方案依赖区分字段（如ParentCode）来指定关联目标表，但本次场景无对应区分字段，且SQLite虚拟列的生成表达式不支持子查询（无法直接判断Origin是否存在于某张表），因此该方案无法直接复用。

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