如何实现基于type_support的参数化外键关联多支撑表？

问题背景 #

每个节点对应一种支撑结构，支撑类型包含墙体（wall）、墩柱（pylon）等共7种。原本设计中，node表通过type_support字段指定关联的支撑表（类似参数化多态），id_support字段对应目标支撑表的主键（如wall表的id_wall、pylon表的id_pylon）。但SQL标准中，外键无法动态指定关联的目标表，原代码中的外键定义无法实现需求：

CREATE TABLE node 
(
    id_node      INTEGER NOT NULL PRIMARY KEY,
    type_support INTEGER NOT NULL,
    id_support   INTEGER NOT NULL,
    -- 此处无法动态指定关联的支撑表
    FOREIGN KEY (id_support) REFERENCES *The_right_support_table(id_the_right_support)*;

当前使用SQLite数据库，后续将迁移至PostgreSQL。

解决方案（采纳@Schwern的方案） #

通过创建基础支撑父表+各类型支撑子表的方式，统一外键关联逻辑，具体实现如下：

1. 创建基础支撑父表 #

先建立一个support表作为所有支撑类型的统一入口，存储支撑的通用标识和类型：

CREATE TABLE support (
    id_support INTEGER NOT NULL PRIMARY KEY,
    type_support INTEGER NOT NULL -- 用数值标识支撑类型，例如1=墙体，2=墩柱，以此类推
    -- 可添加所有支撑类型共有的字段，如创建时间、状态等
);

2. 创建各类型支撑子表 #

每种支撑类型创建专属子表，子表的主键同时作为外键关联到support表的主键，确保支撑记录的唯一性和关联性：

-- 墙体专属表
CREATE TABLE wall (
    id_wall INTEGER NOT NULL PRIMARY KEY,
    -- 添加墙体专属字段，如厚度、材质等
    FOREIGN KEY (id_wall) REFERENCES support(id_support)
);

-- 墩柱专属表
CREATE TABLE pylon (
    id_pylon INTEGER NOT NULL PRIMARY KEY,
    -- 添加墩柱专属字段，如高度、直径等
    FOREIGN KEY (id_pylon) REFERENCES support(id_support)
);

3. 修改节点表关联逻辑 #

node表不再需要type_support字段，直接通过id_support关联到统一的support表即可：

CREATE TABLE node (
    id_node INTEGER NOT NULL PRIMARY KEY,
    id_support INTEGER NOT NULL,
    FOREIGN KEY (id_support) REFERENCES support(id_support)
);

方案优势 #

• 兼容SQLite与PostgreSQL：两种数据库均支持这种父子表的外键关联逻辑，后续迁移无需大幅调整
• 扩展性强：新增支撑类型时，只需创建对应的子表并关联support表，无需修改node表结构
• 数据完整性保障：所有支撑记录必须先在support表中注册，子表与父表的主键一一绑定，避免无效的关联ID
• 逻辑清晰：通过support表的type_support字段即可区分支撑类型，关联查询时可通过JOIN子表获取专属数据

可选增强（PostgreSQL/SQLite通用） #

若需要确保一个支撑记录仅属于一种支撑类型（避免同一id_support同时出现在多个子表中），可通过触发器实现校验逻辑。例如在PostgreSQL中，可给每个子表添加插入/更新触发器，检查该id_support未被其他子表使用。

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