关系设计与数据库约束
🎯 引言
学完这篇文章,你能用关联表表达用户关系,并防止重复或无效的关联记录。
🧱 同一份信息放在哪里
在文章里保存作者编号,姓名放在用户表。作者改名时只修改用户表,文章查询通过编号取得新姓名。这是拆表的直接用途:减少同一份资料在多个地方不一致。
一个用户可以写多篇文章,叫一对多;一个用户可以加入多个小组,一个小组也可以有多个用户,叫多对多。多对多需要一张表记录“谁加入了哪个小组”,不能把一串用户编号塞进一个文本字段。
👥 先建小组表
本篇使用 blog_demo.user 中编号 1 的用户,只展示这次关联需要的字段:
| id | username |
|---|---|
| 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 现在保存这两个小组:
| id | name |
|---|---|
| 1 | SQL 学习组 |
| 2 | Node 学习组 |
🔗 关联表每行表示一次加入
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_id | user_id |
|---|---|
| 1 | 1 |
这行表示“小明加入 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_id | study_group.id | 1、2 |
FOREIGN KEY 后面写要检查的列,REFERENCES 后面写提供有效编号的表和列。fk_member_group 只是这条规则的名字,便于在报错或修改约束时识别它。
尝试加入不存在的小组
INSERT INTO group_member (group_id, user_id) VALUES (999, 1);
MySQL 会报外键约束错误,通常包含错误码 1452。这条记录没有插入成功,成员表仍然只有原来的记录:
| group_id | user_id |
|---|---|
| 1 | 1 |
因为 study_group.id 中没有 999,数据库拒绝保存“加入小组 999”这件事。
加入已经存在的小组就能成功
INSERT INTO group_member (group_id, user_id) VALUES (2, 1);
小组 2 存在,而且这对编号还没有重复,所以写入成功。成员表变为:
| group_id | user_id |
|---|---|
| 1 | 1 |
| 2 | 1 |
小明现在加入了两个小组。前面的联合主键检查“是否重复加入”,这里的外键检查“小组是否存在”,两条规则负责不同的问题。
用户编号也要单独检查
刚才只检查了小组。要防止一个不存在的用户成为成员,还需另一条外键:
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 或记录验证结果
