sql 外鍵

當我們用主鍵唯一辨別記錄時，我們就可以在​

​students​

​表中确定任意一個學生的記錄：

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

我們還可以在​

​classes​

​表中确定任意一個班級記錄：

	一班
	二班

但是我們如何确定​

​students​

​表的一條記錄，例如，​

​id=1​

​的小明，屬于哪個班級呢？

由于一個班級可以有多個學生，在關系模型中，這兩個表的關系可以稱為“一對多”，即一個​

​classes​

​的記錄可以對應多個​

​students​

​表的記錄。

為了表達這種一對多的關系，我們需要在​

​students​

​表中加入一列​

​class_id​

​，讓它的值與​

​classes​

​表的某條記錄相對應：

	class_id
5		小白

這樣，我們就可以根據​

​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​

​表：

	張老師
	王老師
3	李老師
4	趙老師

​classes​

中間表​

​teacher_class​

​關聯兩個一對多關系：

	teacher_id
6

通過中間表​

​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​

​，我們就可以得到一個“一對一”關系​

	student_id	mobile
		135xxxx6300
		138xxxx2209
		139xxxx8086

有細心的童鞋會問，既然是一對一關系，那為啥不給​

​students​

​表增加一個​

​mobile​

​列，這樣就能合二為一了？

如果業務允許，完全可以把兩個表合為一個表。但是，有些時候，如果某個學生沒有手機号，那麼，​

​contacts​

​表就不存在對應的記錄。實際上，一對一關系準确地說，是​

​contacts​

​表一對一對應​

​students​

​表。

還有一些應用會把一個大表拆成兩個一對一的表，目的是把經常讀取和不經常讀取的字段分開，以獲得更高的性能。例如，把一個大的使用者表分拆為使用者基本資訊表​

​user_info​

​和使用者詳細資訊表​

​user_profiles​

​，大部分時候，隻需要查詢​

​user_info​

​表，并不需要查詢​

​user_profiles​

​表，這樣就提高了查詢速度。

小結