1月阅读周·MySQL数据库入门:多表操作之外键篇
背景
去年下半年,我在微信书架里加入了许多技术书籍,各种类别的都有,断断续续的读了一部分。
没有计划的阅读,收效甚微。
新年伊始,我准备尝试一下其他方式,比如阅读周。每月抽出1~2个非连续周,完整阅读一本书籍。
这个“玩法”虽然常见且板正,但是有效,已经坚持阅读十二个月。
已读完书籍:《架构简洁之道》、《深入浅出的Node.js》、《你不知道的JavaScript(上卷)》、《你不知道的JavaScript(中卷)》、《你不知道的JavaScript(下卷)》、《数据结构与算法JavaScript描述》、《WebKit技术内幕》、《前端架构:从入门到微前端》、《秒懂算法:用常识解读数据结构与算法》、《JavaScript权威指南》、《JavaScript异步编程设计快速响应的网络应用》、《编写可测试的JavaScript代码》。
当前阅读周书籍:《MySQL数据库入门》。
外键
在实际开发的项目中,一个健壮数据库中的数据一定有很好的参照完整性。例如,有学生档案和成绩单两张表,如果成绩单中有张三的成绩,学生档案中张三的档案却被删除了,这样就会产生垃圾数据或者错误数据。为了保证数据的完整性,将两表之间的数据建立关系,因此就需要在成绩表中添加外键约束。
什么是外键
外键是指引用另一个表中的一列或多列,被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。为了使初学者更好地理解外键的定义,接下来,通过两张表来讲解什么是外键。
首先需要创建两个表,一个班级表(grade)和一个学生表(student),具体语句如下:
CREATE DATABASE chapter05;
USE chapter05;
CREATE TABLE grade(
id int(4) NOT NULL PRIMARY KEY,
name varchar(36)
);
CREATE TABLE student(
sid int(4) NOT NULL PRIMARY KEY,
sname varchar(36),
gid int(4) NOT NULL
);
学生表(student)中的gid是学生所在的班级id,是引入了班级表(grade)中的主键id。那么gid就可以作为表student的外键。被引用的表,即表grade是主表;引用外键的表,即表student是从表,两个表是主从关系。表student用gid可以连接表grade中的信息,从而建立了两个表数据之间的连接。
引入外键后,外键列只能插入参照列存在的值,参照列被参照的值不能被删除,这就保证了数据的参照完整性。
为表添加外键约束
我们已经知道了什么是外键,想要真正连接两个表的数据,就需要为表添加外键约束。为表添加外键约束的语法格式如下:
alter table 表名 add constraint FK_ID foreign key(外键字段名) REFERENCES 外表表名(主键字段名);
接下来,为表student添加外键约束,具体语句如下:
alter table student add constraint FK_ID foreign key(gid) REFERENCES grade (id);
语句执行成功后,使用DESC语句来查看学生表和班级表,查询结果如下:
mysql>desc grade;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(4) | NO | PRI | NULL | |
| name | varchar(36) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)
mysql>desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(4) | NO | PRI | NULL | |
| sname | varchar(36) | YES | | NULL | |
| gid | int(4) | NO | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
从查询结果可以看出,上面创建的grade表和student表都创建成功了,并且grade表中的id为主键,student表中的gid为外键。但是结果中不能明确地看出两个表之间的关系。在MySQL中可以用show create table 来查看表的详细结构,具体语句如下:
show create table student;
查询结果如下:
mysql>show create table student;
+---------+--------------------------------+
| Table | Create Table
+---------+--------------------------------+
| student | CREATE TABLE 'student' (
'sid' int(4) NOT NULL,
'sname' varchar(36) DEFAULT NULL,
'gid' int(4) NOT NULL,
PRIMARY KEY ('sid'),
KEY 'FK_ID' ('gid'),
CONSTRAINT 'FK_ID' FOREIGN KEY ('gid') REFERENCES 'grade' ('id')
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+---------+--------------------------------+
1 row in set (0.00 sec)
从查询结果可以明确地看出,gid为student表的外键,并且gid外键依赖于grade表中的id主键,这样两个表就通过外键关联起来了。
在为表添加外键约束时,有些需要注意的地方,如下所示。
(1)建立外键的表必须是InnoDB型,不能是临时表。因为在MySQL中只有InnoDB类型的表才支持外键。
(2)定义外键名时,不能加引号,如constraint 'FK_ID'或constraint "FK_ID"都是错误的。
多学一招:添加外键约束的参数说明
我们知道建立外键是为了保证数据的完整和统一性,但如果主表中的数据被删除或修改,从表中对应的数据该怎么办?很明显,从表中对应的数据也应该被删除,否则数据库中会存在很多无意义的垃圾数据。MySQL可以在建立外键时添加ON DELETE或ON UPDATE子句来告诉数据库,怎样避免垃圾数据的产生。具体语法格式如下:
alter table 表名 add constraint FK_ID foreign key(外键字段名) REFERENCES 外表表名(主键字段名);
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
语句中各参数的具体说明如表1-1所示。
表1-1 添加外键约束的参数说明
删除外键约束
在实际开发中,根据业务逻辑的需求,需要解除两个表之间的关联关系时,就需要删除外键约束。删除外键约束的语法格式如下:
alter table表名drop foreign key 外键名;
接下来,将表student中的外键约束删除,具体语句如下:
alter table student drop foreign key FK_ID;
语句执行成功后,查看表student现在的详细结构,查询结果如下:
mysql>show create table student;
+---------+--------------------------------------------+
| Table | Create Table
+---------+--------------------------------------------+
| student | CREATE TABLE 'student' (
'sid' int(4) NOT NULL,
'sname' varchar(36) DEFAULT NULL,
'gid' int(4) NOT NULL,
PRIMARY KEY ('sid'),
KEY 'FK_ID' ('gid')
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+---------+--------------------------------------------+
1 row in set (0.00 sec)
从查询结果可以看出,表student中的外键约束已经被成功删除。
总结
外键是指引用另一个表中的一列或多列,被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。
在实际开发的项目中,一个健壮数据库中的数据一定有很好的参照完整性。例如,有学生档案和成绩单两张表,如果成绩单中有张三的成绩,学生档案中张三的档案却被删除了,这样就会产生垃圾数据或者错误数据。为了保证数据的完整性,将两表之间的数据建立关系,因此就需要在成绩表中添加外键约束。
作者介绍
非职业「传道授业解惑」的开发者叶一一。
《趣学前端》、《CSS畅想》等系列作者。华夏美食、国漫、古风重度爱好者,刑侦、无限流小说初级玩家。
如果看完文章有所收获,欢迎点赞👍 | 收藏⭐️ | 留言📝。
- 点赞
- 收藏
- 关注作者
评论(0)