如何为可变外键列表维护参照完整性并合理建模数据？

基础表结构 #

首先是存储科目、教师及两者关联关系的基础表：

CREATE TABLE subject (
    id bigint PRIMARY KEY,
    name text UNIQUE NOT NULL,
    description text
);

CREATE TABLE teacher (
    id bigint PRIMARY KEY,
    full_name text NOT NULL
);

CREATE TABLE teacher_subject(
    id bigint PRIMARY KEY,
    teacher_id bigint NOT NULL,
    subject_id bigint NOT NULL,
    UNIQUE(teacher_id, subject_id),
    FOREIGN KEY (teacher_id) REFERENCES teacher (id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subject (id) ON DELETE CASCADE
);

业务场景 #

学生提交科目学习申请时，需先选择目标科目，系统展示该科目下的所有授课教师，学生可取消勾选不想跟随的教师。最终要存储该申请数据，同时必须保证：

• 偏好的教师确实教授该申请的科目
• 维护与teacher、teacher_subject表的参照完整性
• 限制学生同一科目仅能提交一次申请

现有尝试方案的问题 #

方案1：直接关联教师ID #

最初设计preferred_teachers表关联teacher_id，但无法验证该教师是否教授申请的科目，会出现教师与科目不匹配的无效数据。

方案2：关联teacher_subjectID但保留申请表的科目ID #

调整后关联teacher_subject_id，但无法约束teacher_subject中的科目ID与subject_application的科目ID一致，仍可能出现申请物理科目但关联数学教师的错误。

方案3：移除申请表的科目ID #

将科目信息完全依赖preferred_teachers的teacher_subject_id，但无法保证单份申请对应单一科目，也无法限制学生重复提交同一科目的申请，不符合业务规则。

纯SQL建模解决方案 #

核心思路是通过复合外键约束，同时绑定申请的科目与教师的授课科目，确保数据一致性，无需触发器。

最终表结构 #

-- 学生科目申请主表
CREATE TABLE subject_application (
    id bigint PRIMARY KEY,
    student_id bigint NOT NULL,
    subject_id bigint NOT NULL,
    -- 限制同一学生同一科目仅提交一次申请
    UNIQUE(student_id, subject_id),
    FOREIGN KEY (student_id) REFERENCES student (id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subject (id) ON DELETE CASCADE
);

-- 学生偏好教师表
CREATE TABLE preferred_teachers (
    id bigint PRIMARY KEY,
    subject_application_id bigint NOT NULL,
    teacher_id bigint NOT NULL,
    subject_id bigint NOT NULL,
    -- 限制同一申请下同一教师仅被选择一次
    UNIQUE(subject_application_id, teacher_id),
    -- 确保偏好记录的科目与申请的科目完全一致
    FOREIGN KEY (subject_application_id, subject_id) 
        REFERENCES subject_application (id, subject_id) 
        ON DELETE CASCADE,
    -- 确保该教师确实教授对应科目（依赖teacher_subject的唯一约束）
    FOREIGN KEY (teacher_id, subject_id) 
        REFERENCES teacher_subject (teacher_id, subject_id) 
        ON DELETE CASCADE
);

方案说明 #

1. 冗余科目ID：在preferred_teachers中冗余subject_id，用于构建复合外键，这是实现纯SQL约束的关键。
2. 复合外键1：(subject_application_id, subject_id)绑定申请记录与偏好记录的科目，确保两者科目完全一致。
3. 复合外键2：(teacher_id, subject_id)关联teacher_subject的唯一约束，确保该教师确实教授该科目。
4. 唯一性约束：subject_application的UNIQUE(student_id, subject_id)限制学生同一科目仅提交一次；preferred_teachers的UNIQUE(subject_application_id, teacher_id)避免同一申请重复选择同一教师。
5. 级联删除：所有外键均配置ON DELETE CASCADE，确保教师、科目、申请被删除时，对应的偏好记录自动清理。

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