关系设计与数据库约束

用关联表表达用户关系,并防止重复或无效的关联记录

🎯 引言

学完这篇文章,你能用关联表表达用户关系,并防止重复或无效的关联记录。


🧱 同一份信息放在哪里

在文章里保存作者编号,姓名放在用户表。作者改名时只修改用户表,文章查询通过编号取得新姓名。这是拆表的直接用途:减少同一份资料在多个地方不一致。

一个用户可以写多篇文章,叫一对多;一个用户可以加入多个小组,一个小组也可以有多个用户,叫多对多。多对多需要一张表记录“谁加入了哪个小组”,不能把一串用户编号塞进一个文本字段。


👥 先建小组表

本篇使用 blog_demo.user 中编号 1 的用户,只展示这次关联需要的字段:

idusername
1小明

其他用户和字段不影响本例。接着创建小组:

CREATE TABLE study_group (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

插入两个小组:

INSERT INTO study_group VALUES (1, 'SQL 学习组'), (2, 'Node 学习组');

study_group 现在保存这两个小组:

idname
1SQL 学习组
2Node 学习组

🔗 关联表每行表示一次加入

CREATE TABLE group_member (
    group_id INT NOT NULL,
    user_id INT NOT NULL,
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (group_id, user_id)
);

两列合起来组成主键:同一用户可以加入多个组,但同一个“组 + 用户”不能出现两次。joined_at 属于这次加入关系,因此放在关联表里。

添加成员:

INSERT INTO group_member (group_id, user_id) VALUES (1, 1);

group_member 中新增以下记录,只展示两个编号字段:

group_iduser_id
11

这行表示“小明加入 SQL 学习组”:group_id 为 1,对应小组表的 SQL 学习组;user_id 为 1,对应用户表的小明。

重复执行会得到重复键错误,不会出现两条相同关系。即使两个请求同时提交,数据库也会拒绝重复组合,不能只依赖应用先查询一次。


🛡 外键防止关联到不存在的数据

小组表里只有编号 1 和 2。如果成员表保存了“用户 1 加入小组 999”,这条记录就没有意义,因为根本没有小组 999。

外键就是让数据库检查这种对应关系的规则:写入成员表的小组编号,必须能在小组表中找到。仅仅把字段命名为 group_id 不会自动产生这项检查,需要明确添加外键。

让成员表的小组编号对应已有小组

ALTER TABLE group_member ADD CONSTRAINT fk_member_group
    FOREIGN KEY (group_id) REFERENCES study_group(id);

这条语句的意思是:给 group_member 增加一条名为 fk_member_group 的规则,检查它的 group_id 是否出现在 study_group 的 id 列中。

写入哪一列去哪里检查本例允许的编号
group_member.group_idstudy_group.id1、2

FOREIGN KEY 后面写要检查的列,REFERENCES 后面写提供有效编号的表和列。fk_member_group 只是这条规则的名字,便于在报错或修改约束时识别它。

尝试加入不存在的小组

INSERT INTO group_member (group_id, user_id) VALUES (999, 1);

MySQL 会报外键约束错误,通常包含错误码 1452。这条记录没有插入成功,成员表仍然只有原来的记录:

group_iduser_id
11

因为 study_group.id 中没有 999,数据库拒绝保存“加入小组 999”这件事。

加入已经存在的小组就能成功

INSERT INTO group_member (group_id, user_id) VALUES (2, 1);

小组 2 存在,而且这对编号还没有重复,所以写入成功。成员表变为:

group_iduser_id
11
21

小明现在加入了两个小组。前面的联合主键检查“是否重复加入”,这里的外键检查“小组是否存在”,两条规则负责不同的问题。

用户编号也要单独检查

刚才只检查了小组。要防止一个不存在的用户成为成员,还需另一条外键:

ALTER TABLE group_member ADD CONSTRAINT fk_member_user
    FOREIGN KEY (user_id) REFERENCES user(id);

这次检查的是 group_member.user_id,要求它能在 user.id 中找到。例如本课程没有用户 999,写入“小组 1、用户 999”就会被拒绝。小组存在,并不代表用户也存在,两列需要分别检查。

添加外键时,数据库也会检查表中已有的记录;如果之前已经保存了不存在的小组或用户编号,需要先修正这些记录。本例两侧编号均使用 INT,类型保持一致。


🗑 删除时明确要保留什么

本例没有设置级联删除。小组 1 仍有成员记录时,直接删除小组会被外键拒绝,避免留下指向不存在小组的关系。

如果确实要移除该组,需要先删除成员关系,再删除小组,并把两步放进下一篇的事务中。对于“删除父记录就应该自动删除全部关系”的场景,可以在设计外键时选择 ON DELETE CASCADE;必须先确认关联数据也应该消失。

如果只想让小组暂时不再展示,可以添加状态字段并在查询时过滤,这叫软删除或停用。它保留原数据,但所有相关查询都要遵守过滤规则,且原来的唯一约束仍然有效。


🧭 哪些重复值值得保留

实时资料一般通过关联读取,避免复制。若需要保留历史,例如记录用户操作当时显示的昵称,可以在操作记录中保存当时的值,并明确它不会随用户改名更新。

先判断要查询“现在是什么”还是“当时是什么”,再决定是否重复保存,而不是为了少写一次 JOIN 就复制所有字段。


❓ 知识问答

Q1:所有表都必须关联吗?

不必。表之间确实存在业务关系时再建立关联,日志等独立记录不必强行连接所有表。

Q2:外键会自动返回用户资料吗?

不会。外键负责数据合法性,读取关联资料仍需要 JOIN 或其他查询。

Q3:应用已经检查用户存在,还需要外键吗?

两者目的不同。应用提供友好提示,外键还能防住并发删除或其他程序直接写入造成的无效关系。


🧪 小练习

尝试让用户 999 加入小组 1,确认被用户外键拒绝;再让用户 1 重复加入小组 1,比较这次错误与前一次有什么不同。练习前确认 user 表没有编号 999 的记录。

-- 请在这里编写 SQL 或记录验证结果