SQL关系模型-外键

我就是我 2024-03-31 11:58 76阅读 0赞

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

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>other columns...</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>小明</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>小红</td> 
   <td>...</td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>other columns...</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>一班</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>二班</td> 
   <td>...</td> 
  </tr> 
 </tbody> 
</table>

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

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

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

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>class_id</th> 
   <th>name</th> 
   <th>other columns...</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>小明</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
   <td>小红</td> 
   <td>...</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>2</td> 
   <td>小白</td> 
   <td>...</td> 
  </tr> 
 </tbody> 
</table>

这样，我们就可以根据`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`表：

`classes`表：

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

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>teacher_id</th> 
   <th>class_id</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>2</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>3</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>4</td> 
   <td>2</td> 
  </tr> 
 </tbody> 
</table>

通过中间表`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`，我们就可以得到一个“一对一”关系：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>student_id</th> 
   <th>mobile</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>135xxxx6300</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>2</td> 
   <td>138xxxx2209</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>5</td> 
   <td>139xxxx8086</td> 
  </tr> 
 </tbody> 
</table>

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

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

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

#### 小结 ####

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