SQL 外键知识

文章目录

• 多对多
• 一对一
• 小结

sql 外键

当我们用主键唯一标识记录时，我们就可以在​​students​​表中确定任意一个学生的记录：

id	name	other columns...
1	小明	...
2	小红	...

我们还可以在​​classes​​表中确定任意一个班级记录：

id	name	other columns...
1	一班	...
2	二班	...

但是我们如何确定​​students​​表的一条记录，例如，​​id=1​​的小明，属于哪个班级呢？

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个​​classes​​的记录可以对应多个​​students​​表的记录。

为了表达这种一对多的关系，我们需要在​​students​​表中加入一列​​class_id​​，让它的值与​​classes​​表的某条记录相对应：

id	class_id	name	other columns...
1	1	小明	...
2	1	小红	...
5	2	小白	...

这样，我们就可以根据​​class_id​​这个列直接定位出一个​​students​​表的记录应该对应到​​classes​​的哪条记录。

例如：

• 小明的​​class_id​​是​​1​​，因此，对应的​​classes​​表的记录是​​id=1​​的一班；
• 小红的​​class_id​​是​​1​​，因此，对应的​​classes​​表的记录是​​id=1​​的一班；
• 小白的​​class_id​​是​​2​​，因此，对应的​​classes​​表的记录是​​id=2​​的二班。

在​​students​​表中，通过​​class_id​​的字段，可以把数据与另一张表关联起来，这种列称为​​外键​​。

外键并不是通过列名实现的，而是通过定义外键约束实现的：

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

 

其中，外键约束的名称​​fk_class_id​​可以任意，​​FOREIGN KEY (class_id)​​指定了​​class_id​​作为外键，​​REFERENCES classes (id)​​指定了这个外键将关联到​​classes​​表的​​id​​列（即​​classes​​表的主键）。

通过定义外键约束，关系数据库可以保证无法插入无效的数据。即如果​​classes​​表不存在​​id=99​​的记录，​​students​​表就无法插入​​class_id=99​​的记录。

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，​​class_id​​仅仅是一个普通的列，只是它起到了外键的作用而已。

要删除一个外键约束，也是通过​​ALTER TABLE​​实现的：

ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

 

注意：删除外键约束并没有删除外键这一列。删除列是通过​​DROP COLUMN ...​​实现的。

多对多

通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义“多对多”关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：

​​teachers​​表：

id	name
1	张老师
2	王老师
3	李老师
4	赵老师

​​classes​​表：

id	name
1	一班
2	二班

中间表​​teacher_class​​关联两个一对多关系：

id	teacher_id	class_id
1	1	1
2	1	2
3	2	1
4	2	2
5	3	1
6	4	2

通过中间表​​teacher_class​​可知​​teachers​​到​​classes​​的关系：

• ​​id=1​​的张老师对应​​id=1,2​​的一班和二班；
• ​​id=2​​的王老师对应​​id=1,2​​的一班和二班；
• ​​id=3​​的李老师对应​​id=1​​的一班；
• ​​id=4​​的赵老师对应​​id=2​​的二班。

同理可知​​classes​​到​​teachers​​的关系：

• ​​id=1​​的一班对应​​id=1,2,3​​的张老师、王老师和李老师；
• ​​id=2​​的二班对应​​id=1,2,4​​的张老师、王老师和赵老师；

因此，通过中间表，我们就定义了一个“多对多”关系。

一对一

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

例如，​​students​​​表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表​​contacts​​，我们就可以得到一个“一对一”关系​

id	student_id	mobile
1	1	135xxxx6300
2	2	138xxxx2209
3	5	139xxxx8086

有细心的童鞋会问，既然是一对一关系，那为啥不给​​students​​表增加一个​​mobile​​列，这样就能合二为一了？

如果业务允许，完全可以把两个表合为一个表。但是，有些时候，如果某个学生没有手机号，那么，​​contacts​​表就不存在对应的记录。实际上，一对一关系准确地说，是​​contacts​​表一对一对应​​students​​表。

还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表​​user_info​​和用户详细信息表​​user_profiles​​，大部分时候，只需要查询​​user_info​​表，并不需要查询​​user_profiles​​表，这样就提高了查询速度。

小结

关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。