外键约束创建失败排查及第三范式(3NF)校验技术问询

四张表的中文说明与创建语句 #

表A：作者信息

CREATE TABLE TableA (
    AuthorLastName VARCHAR(255), -- 作者姓氏
    AuthorFirstName VARCHAR(255), -- 作者名字
    BookTitle VARCHAR(255), -- 书籍标题
    PRIMARY KEY (AuthorLastName, AuthorFirstName, BookTitle)
);

表B：书籍信息

CREATE TABLE TableB (
    BookTitle VARCHAR(255), -- 书籍标题
    Subject VARCHAR(255), -- 主题
    PRIMARY KEY (BookTitle, Subject),
    FOREIGN KEY (BookTitle) REFERENCES TableA(BookTitle)
);

表C：主题与馆藏位置

CREATE TABLE TableC (
    Subject VARCHAR(255), -- 主题
    CollectionorLocation VARCHAR(255), -- 馆藏位置
    PRIMARY KEY (Subject, CollectionorLocation),
    FOREIGN KEY (Subject) REFERENCES TableB(Subject)
);

表D：馆藏位置与建筑

CREATE TABLE TableD (
    CollectionorLocation VARCHAR(255), -- 馆藏位置
    Building VARCHAR(255), -- 建筑
    PRIMARY KEY (CollectionorLocation, Building),
    FOREIGN KEY (CollectionorLocation) REFERENCES TableC(CollectionorLocation)
);

核心问题：外键引用违反MySQL约束规则 #

MySQL对外键的创建有严格要求：外键必须关联被引用表的主键，或者被引用列有唯一约束。当前三张表的外键设置全部违反了这个规则：

• TableB创建失败：TableB试图用BookTitle关联TableA，但TableA的主键是复合主键(AuthorLastName, AuthorFirstName, BookTitle)，单独的BookTitle既不是主键，也没有唯一约束，无法作为外键的关联目标。
• TableC、TableD的同类错误：TableC用Subject关联TableB，而TableB的主键是(BookTitle, Subject)，单独的Subject不是主键/唯一列；TableD用CollectionorLocation关联TableC，TableC的主键是(Subject, CollectionorLocation)，单独的CollectionorLocation同样不满足外键要求，导致约束创建失败。

当前表结构完全不符合3NF（第三范式），核心问题是数据依赖关系混乱，存在大量冗余和非主键列的非直接依赖：

TableA（作者信息） #

• 主键是(AuthorLastName, AuthorFirstName, BookTitle)，但BookTitle并不依赖于作者信息——同一本书可以有多个作者，这会导致同一书名被重复存储多次；同时作者信息也不依赖于书名，属于部分依赖，违反2NF（进而违反3NF）。
• 合理设计：拆分出三张表：
  1. 作者表：存储作者ID、姓氏、名字（主键为作者ID）
  2. 书籍表：存储书籍ID、书名（主键为书籍ID）
  3. 作者书籍关联表：存储作者ID、书籍ID（记录作者与书籍的对应关系）

TableB（书籍信息） #

• 主键是(BookTitle, Subject)，但Subject和BookTitle是多对多关系，两者互相不依赖，存在部分依赖；加上外键引用本身不合法，结构逻辑完全混乱，不符合3NF。
• 合理设计：拆分出两张表：
  1. 主题表：存储主题ID、主题名称（主键为主题ID）
  2. 书籍主题关联表：存储书籍ID、主题ID（记录书籍与主题的对应关系）

TableC（主题与馆藏位置） #

• 主键是(Subject, CollectionorLocation)，馆藏位置和主题是多对多关系，两者互相不依赖，属于部分依赖；而且馆藏位置是独立实体，不应该依赖于主题，违反3NF。
• 合理设计：拆分出两张表：
  1. 馆藏位置表：存储位置ID、位置名称（主键为位置ID）
  2. 主题位置关联表：存储主题ID、位置ID（记录主题与馆藏位置的对应关系）

TableD（馆藏位置与建筑） #

• 主键是(CollectionorLocation, Building)，如果一个馆藏位置只属于一个建筑，那么Building完全依赖于CollectionorLocation，此时复合主键完全多余，存在冗余；如果一个位置对应多个建筑，逻辑上也不合理，整体不符合3NF。
• 合理设计：将Building作为馆藏位置表的一个字段，依赖于位置主键。

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