如何规范化两级多对一关系（含可选关系）并实现跨表约束？

针对你提出的需求——让C表的b_id仅关联到与自身a_id匹配的B表行，同时保持数据库规范化，这里有一个简洁且符合数据库设计规范的解决方案：

核心思路：复合外键约束+唯一约束 #

单一外键只能保证b_id存在于B表，但无法校验a_id是否匹配。我们需要利用复合外键同时约束两个字段的关联关系，结合B表的复合唯一键来实现行级的匹配校验。

具体SQL实现 #

先创建基础的A表：

CREATE TABLE A (
    id INTEGER PRIMARY KEY
);

然后创建B表，显式添加(a_id, id)的唯一约束（由于id是主键，这个组合天然唯一，但显式声明能让后续外键关联更清晰）：

CREATE TABLE B (
    id INTEGER PRIMARY KEY,
    a_id INTEGER NOT NULL REFERENCES A(id),
    UNIQUE(a_id, id) -- 声明复合唯一键，作为C表外键的关联目标
);

最后创建C表，通过复合外键约束关联B表的复合唯一键：

CREATE TABLE C (
    id INTEGER PRIMARY KEY,
    a_id INTEGER NOT NULL REFERENCES A(id),
    b_id INTEGER, -- 可选字段，允许为NULL
    -- 关键约束：确保b_id对应的B行a_id与当前C行a_id完全一致
    FOREIGN KEY (a_id, b_id) REFERENCES B(a_id, id)
);

为什么这个方案符合要求？ #

• 数据一致性保障：数据库会自动校验插入/更新操作，只要b_id不为NULL，就必须对应B表中a_id与C行a_id相同的记录，完全满足你的约束需求。
• 保持规范化：所有表都符合第三范式（3NF），没有冗余数据，每个字段都直接依赖主键，不存在传递依赖。
• 支持可选关联：b_id允许为NULL，对应场景中不需要关联B表的情况（比如你的示例中直接属于Folder的File）。

结合你的场景映射 #

把表对应到你的文件夹/文件/标签场景：

• A表 → Folder：存储文件夹的唯一ID
• B表 → SubFolder（标签）：存储标签ID，以及所属的Folder ID，确保每个标签归属于一个文件夹
• C表 → File：存储文件ID、所属Folder ID，可选关联SubFolder ID，且关联的SubFolder必须和文件属于同一个Folder

这种设计完全适配你的业务逻辑，同时保证了数据的完整性和规范性。

补充说明 #

这种方案在PostgreSQL、MySQL 8.0+、SQL Server等主流现代数据库中都能完美支持，相比用触发器实现的方式，数据库原生的约束更可靠，不会被绕过，性能也更好。

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