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??表，这样就提高了查询速度。
小结
关系数据库通过外键可以实现一对多、多对多和一对一的关系。外键既可以通过数据库来约束，也可以不设置约束，仅依靠应用程序的逻辑来保证。