SQL中自然复合键与单一代理键的选择及Lesson表外键引用咨询

先修正你的SQL错误 #

你写的Lesson表外键引用存在问题：foreign key (section_id) references Section(section) 里的Section(section)是不存在的字段，需修改为Section表的合法键，这是首先要修正的基础问题。

两种引用方案的利弊对比 #

方案1：Lesson引用Section的代理键section_surr_id #

• 优势：
  • 外键为单一列，关联查询时语法简洁，例如：JOIN Section s ON l.section_surr_id = s.section_surr_id
  • 代理键是无意义的自增值，后续若业务中需要调整curr_id（如课程大纲ID变更）或section_id（如章节编号重排），不会影响外键关联，数据一致性更稳定
• 劣势：
  • 日常操作时需先查询代理键才能填充，但该问题可通过简单方式解决——创建包含curr_id、section_id、section_surr_id的视图，或封装常用查询片段，快速获取对应代理键

方案2：Lesson引用Section的复合自然键(curr_id, section_id) #

• 优势：
  • 无需查询代理键，直接使用业务层面的课程ID和章节ID即可关联，完全贴合日常操作习惯，比如已知课程1的章节1，可直接在Lesson表中填入对应值
• 劣势：
  • 外键为复合列，关联查询时需编写两个匹配条件，语法繁琐：JOIN Section s ON l.curr_id = s.curr_id AND l.section_id = s.section_id
  • 若后续需修改curr_id或section_id，所有关联的Lesson记录需同步更新，维护成本高，易出现数据不一致问题

推荐方案 #

如果业务中curr_id和section_id基本不会变更（比如课程大纲创建后ID固定、章节编号不再调整），可选择方案2，操作更直接。

但从长期维护和数据稳定性角度，更推荐方案1：

1. 为Section表补全主键定义：primary key (section_surr_id)，同时给(curr_id, section_id)添加唯一约束，确保同一课程下章节ID唯一：unique (curr_id, section_id)
2. 修改Lesson表，新增section_surr_id列，外键关联至Section的section_surr_id；Lesson表的curr_id无需单独存储，可通过关联Section表获取，减少冗余字段
3. 为简化日常查询代理键的操作，创建视图：

CREATE VIEW vw_section_mapping AS
SELECT section_surr_id, curr_id, section_id
FROM Section;

需要查找代理键时，直接查询该视图即可快速对应业务层面的课程和章节ID。

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