【MySQL】在資料表之間做關聯查詢與外鍵約束

日期：

https://www.flickr.com/photos/8058098@N07/2718571627/

資料庫經過正規化後，資料表之間便會產生關聯。本文將介紹關聯查詢，以一張表為基準，去關聯另一張表，藉此將內容組合在一起。接著介紹如何約束好兩張表之間的關聯，確保資料的一致與完整。

本文範例參考影片。

此篇內容轉載自本人在 iThome 的文章。


一、資料表設計

首先認識一下員工資料表的設計。

另外還有一個部門資料表，欄位包含編號、名稱與經理的員工編號。

其中員工表的「dept_id」欄位代表部門編號，會關聯到部門表的「dept_id」欄位。而員工表的「sup_id」欄位代表主管編號，會關聯到另一筆員工資料。

由於 dept_id 與 sup_id 欄位會關聯到另一筆資料的主鍵（primary key），因此這兩個欄位被稱為「外鍵」（foreign key）。


二、關聯查詢

（一）交集關聯

以下指令是查詢每位員工及其所屬部門的資料。

使用 JOIN ... ON ... 語法，將以 FROM 指定的表為基準，去關聯 JOIN 指定的表。而關聯的條件，是員工表的外鍵等於部門表的主鍵。

以下指令是查詢每位員工的主管：

這個例子比較特別，是資料表關聯自己。在撰寫指令時，要當成兩張表，並替它們取「別名」（alias）作為區隔。在此將員工表與主管表分別取為「emp」與「sup」。

另外在指定欄位時，也可使用別名，在 SELECT 與 ON 語法後方都可看見。

（二）左關聯

在前面查詢員工主管的例子中，會發現查不到「小黃」這個人。理由是 JOIN 取的是兩表的「交集」。小黃的 sup_id 外鍵欄位值是 null，對應不到任何資料，因此不會出現在結果中。

若我們希望關聯不到資料的小黃能出現在結果中，那麼可以使用 LEFT JOIN 語法。

LEFT JOIN 的特色，是讓 FROM 指定的表的資料，都能出現在查詢結果中。如果外鍵關聯不到資料，則 JOIN 的表的相關欄位值便留空，也就是 null。

（三）右關聯

其語法為 RIGHT JOIN，用途與 LEFT JOIN 相反。JOIN 指定的表的資料，都會出現在結果中。

這兩者是可以互相改寫的。

筆者認為 LEFT JOIN 更加直覺。


三、外鍵約束

為了在兩張有關聯的表之間，維護資料的一致性與完整性，我們可以在外鍵與主鍵欄位添加「約束」（constraint）。

（一）添加約束

假設 A 表有個外鍵欄位關聯到 B 表主鍵。藉由添加約束，可以確保在 A 表插入或修改資料時，外鍵欄位值均能對應到 B 表的任一資料，達到了一致性。

以下是在建立員工表時添加約束，將部門編號欄位，關聯到部門表的編號欄位。

執行上述指令的前提是部門表「dept」已經存在。然而部門表也有一個經理編號欄位（manager_id），要關聯到員工表的編號欄位（emp_id）。

當兩表互相約束，需透過事後修改資料表的方式來定義。

至於員工表的主管欄位，則屬於自己關聯自己，也可添加約束。

（二）移除約束

在刪除部門資料表時，由於它被具有外鍵的員工表參考，因此會無法刪除。

此時請先確認員工表的創建指令，找出這個約束的名稱：

SHOW CREATE TABLE `employee`;

在查詢結果中的 CONSTRAINT 語法後方，可得知約束的名稱，如「employee_ibfk_1」。

接著再執行如下的語法，即可移除約束：

（三）維護參考完整性

範例中的員工表對部門表進行了外鍵約束。假設直接刪除部門資料，或者更改編號，那就會造成員工資料不完整，畢竟對應不到部門。所以 MySQL 預設會禁止刪除部門資料，或更新其主鍵值。

然而我們可以做進一步的設定，去決定被參考的資料遇到上述的異動時，外部的資料要如何因應。

以下的指令，是讓部門表的編號欄位值更新時，能連動更新該部門的員工資料。而當部門資料被刪除，則將員工資料的部門欄位值設為 null。

此處使用 CASCADE 與 SET NULL 語法來定義處理方式。

同理，以下是針對員工的主管欄位做設定。


四、插入資料到受約束的表

以第一節的測試資料為例，讓我們重新進行插入資料的步驟。

當資料表有設定外鍵約束，在插入時要格外留意，才不會操作失敗。可掌握以下兩個原則：

  • 會被參考的表，要先具備資料。
  • 當兩表互相約束，先挑選其中一表，令其外鍵值為 null，之後再透過更新來補上。

首先插入部門資料，但經理的員工編號（manager_id 欄位）暫時留空。

INSERT INTO `dept` (`dept_id`, `name`, `manager_id`)
VALUES
(1, "研發", null),
(2, "行政", null),
(3, "資訊", null);

接著輪到員工資料。此處先插入沒有主管的「小黃」，再將主管已在資料表中的其他員工陸續插入：

INSERT INTO `employee` (`emp_id`, `name`, `dept_id`, `sup_id`)
VALUES
(206, "小黃", 1, null),
(207, "小綠", 2, 206),
(208, "小黑", 3, 206),
(209, "小白", 3, 207),
(210, "小蘭", 1, 207);

當然也能暫時將主管編號（sup_id 欄位）設為 null，之後再用 UPDATE 指令更新。但這會額外進行寫入操作，因此建議能在插入時完成，就不需留到事後再更新。

最後回到部門表，補上經理的員工編號：

UPDATE `dept`
SET `manager_id` = 206
WHERE `dept_id` = 1;

UPDATE `dept`
SET `manager_id` = 207
WHERE `dept_id` = 2;

UPDATE `dept`
SET `manager_id` = 208
WHERE `dept_id` = 3;

這樣就完成在兩張表、三個約束的情況下，完成插入資料的流程。


上一篇：【MySQL】排序與分頁

下一篇：【MySQL】數值、字串、日期與判斷函數

留言

張貼留言