適合初學者的 MySQL 外部鍵約束完整指南 | 從設定到故障排除

1. 介紹

MySQL 的外部鍵約束（Foreign Key）是資料庫設計中不可或缺的要素。透過活用外部鍵約束，可以定義資料表間的關係性，並維持資料的完整性。本文將從外部鍵約束的基本概念，到具體的設定方法、疑難排解，一直到清楚易懂地說明。

外部鍵約束的目的

外部鍵約束的主要目的是以下三點。

1. 確保資料的一致性
   如果子資料表中註冊的資料在父資料表中不存在，則會產生錯誤。
2. 維持參照完整性
   當父資料表中的資料被變更或刪除時，可以控制對子資料表的影響。
3. 防止設計錯誤
   透過在開發初期階段設定約束，可以防止非預期的資料不整合。

本文可以學到的內容

透過閱讀本文，可以習得以下技能。

• 理解外部鍵約束的基本結構和使用方法
• 掌握實際設定外部鍵時的注意點
• 學習疑難排解的方法，迅速解決問題

2. 外部鍵（Foreign Key）是什麼

外部鍵（Foreign Key）是資料庫中用來連結兩個資料表的關鍵約束之一。透過此機制，可以建構資料表之間的參照關係，並維持資料的一致性和完整性。

外部鍵的基本定義

外部鍵是設定在某個資料表（子資料表）的欄位，參照另一個資料表（父資料表）的欄位時所使用。透過此參照，以下規則會自動適用。

1. 子資料表的欄位只能註冊父資料表中存在的値。
2. 當父資料表的資料被變更或刪除時，其影響會波及子資料表（可透過選項控制行為）。

外部鍵約束的主要優點

利用外部鍵約束，可以獲得以下優點。

1. 維持資料的完整性
   透過嚴格定義資料表之間的關係，可以預防資料不一致的情況。
2. 減輕應用程式的負擔
   因為資料完整性由資料庫端管理，所以應用程式端的檢查處理可以減輕。
3. 提升維護性
   因為資料表設計變得明確，所以系統的維護和運作變得簡單。

外部鍵的結構範例

以下顯示利用外部鍵約束的具體結構範例。

父資料表的建立

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

子資料表的建立（外部鍵約束的設定）

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

在這個範例中，employees資料表的department_id參照departments資料表的id。因此，employees資料表中註冊的每個員工的部門資訊，必須存在於departments資料表內。

3. 外部鍵約束的設定方法

透過設定外部鍵約束，可以保證表格之間的參照完整性。以下將詳細說明 MySQL 中外部鍵約束的具體設定方法，連同語法和範例。

外部鍵約束的基本語法

在 MySQL 中設定外部鍵約束時的基本語法如下所示。

在建立表格時設定外部鍵

CREATE TABLE 子表格名稱 (
    欄位名稱 資料型態,
    FOREIGN KEY (設為外部鍵的欄位名稱) REFERENCES 父表格名稱(父表格的欄位名稱)
    [ON DELETE 選項] [ON UPDATE 選項]
);

在現有表格中新增外部鍵

ALTER TABLE 子表格名稱
ADD CONSTRAINT 外部鍵名稱 FOREIGN KEY (設為外部鍵的欄位名稱)
REFERENCES 父表格名稱(父表格的欄位名稱)
[ON DELETE 選項] [ON UPDATE 選項];

包含外部鍵約束的表格建立範例

以下顯示建立父表格和子表格，並設定外部鍵約束的範例。

父表格的建立

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

子表格的建立（設定外部鍵約束）

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

要點:

• FOREIGN KEY (category_id) REFERENCES categories(id)
  定義 products 表格的 category_id 參照 categories 表格的 id。
• ON DELETE CASCADE
  當父表格（categories）的資料列被刪除時，相關的子表格（products）的資料也會被刪除。
• ON UPDATE CASCADE
  當父表格的資料列被更新時，相關子表格的值也會自動更新。

在現有表格中新增外部鍵約束的範例

若要在已存在的表格中新增外部鍵約束，請使用以下步驟。

範例：外部鍵約束的新增

ALTER TABLE products
ADD CONSTRAINT fk_category
FOREIGN KEY (category_id)
REFERENCES categories(id)
ON DELETE SET NULL
ON UPDATE CASCADE;

要點:

• fk_category 是外部鍵約束的名稱。當有多個約束時，便於管理。
• ON DELETE SET NULL 透過此設定，當父表格的資料列被刪除時，products 表格的 category_id 會設為 NULL。

4. 外部鍵的動作選項

MySQL 的外部鍵約束中，可以控制當父表格中的資料被變更或刪除時，對子表格產生何種影響。此控制使用 ON DELETE 和 ON UPDATE 的選項來設定。本節將詳細說明各選項，並提供具體範例。

主要選項的種類與行為

以下是可使用 ON DELETE 及 ON UPDATE 選項設定的主要動作。

1. CASCADE（級聯）

• 當父表格的資料被刪除或更新時，子表格的對應資料也會自動被刪除或更新。

1. SET NULL（設為 NULL）

• 當父表格的資料被刪除或更新時，子表格的對應外部鍵值會變成 NULL。子表格的外部鍵必須允許 NULL 值。

1. RESTRICT（限制）

• 當試圖刪除或更新父表格的資料時，若子表格中存在對應資料，則該操作會被拒絕。

1. NO ACTION（無動作）

• 即使父表格的資料被刪除或更新，子表格也不會受到影響。不過，若參照完整性被破壞，則會發生錯誤。

各選項的使用範例

1. CASCADE

當父表格的資料被刪除時，自動刪除子表格相關資料的範例：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT
);

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
ON UPDATE CASCADE;

• 範例：從 customers 表格刪除資料列時，會自動刪除相關的 orders 表格資料。

2. SET NULL

當父表格的資料被刪除時，將子表格的外部鍵設為 NULL 的範例：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

• 範例：刪除 customers 表格的資料時，orders 表格的 customer_id 會變成 NULL。

3. RESTRICT

限制父表格資料刪除與更新的範例：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);

• 範例：若 customers 表格的資料列被 orders 表格參照，則無法進行刪除或更新。

4. NO ACTION

維持參照完整性但不產生特定影響的範例：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

• 範例：即使在父表格中刪除或更新資料，子表格也不會受到變更。不過，若參照完整性被破壞，則會發生錯誤。

選項選擇的最佳實務

• 依業務規則選擇：需根據業務邏輯選擇最適合的選項。例如，若需要連動刪除，則適合使用 CASCADE；若要防止刪除，則適合使用 RESTRICT。
• 需謹慎設計：不必要地使用 CASCADE 可能導致意外的資料遺失。

5. 外部鍵約束的疑難排解

如果在 MySQL 中設定了外部鍵約束，某些操作可能會引發錯誤。透過了解這些錯誤的原因並適當處理，可以順利進行資料庫的設計與運作。本節將說明常見錯誤的範例及其解決方法。

外部鍵約束相關的主要錯誤

1. 資料類型不一致

作為外部鍵參照的欄位資料類型，在父表格與子表格中不一致時會發生。錯誤訊息範例:

ERROR 1215 (HY000): Cannot add foreign key constraint

原因:

• 父表格與子表格的欄位資料類型不同（例如：父為INT，子為VARCHAR）。
• 欄位的屬性（UNSIGNED 等）不同。

解決方法:

• 讓父表格與子表格的欄位資料類型與屬性一致。

CREATE TABLE parent (
    id INT UNSIGNED PRIMARY KEY
);

CREATE TABLE child (
    parent_id INT UNSIGNED,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

2. 參照的資料不存在

試圖插入子表格的資料時，若該資料在父表格中不存在，則會發生。錯誤訊息範例:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

原因:

• 子表格的外部鍵對應的值，在父表格中不存在。

解決方法:

1. 在父表格中新增對應的資料。

   INSERT INTO parent (id) VALUES (1);

2. 在子表格中插入資料。

   INSERT INTO child (parent_id) VALUES (1);

3. 外部鍵約束刪除相關的錯誤

試圖刪除設定了外部鍵約束的父表格資料時，可能會發生錯誤。錯誤訊息範例:

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

原因:

• 子表格中存在參照父表格資料的列。

解決方法:

• 適當設定外部鍵約束的ON DELETE 選項（例如：CASCADE 或 SET NULL）。
• 手動刪除子表格的資料後，再刪除父表格的資料。

   DELETE FROM child WHERE parent_id = 1;
   DELETE FROM parent WHERE id = 1;

確認外部鍵約束問題的方法

1. 確認外部鍵約束的狀態

使用下列查詢來確認表格的外部鍵約束。

SHOW CREATE TABLE table_name;

2. 確認錯誤記錄

錯誤記錄中可能記載問題的詳細資訊。要啟用記錄確認，請在 MySQL 設定中啟用錯誤記錄。

外部鍵約束的暫時停用

在插入或刪除大量資料時，外部鍵約束可能會成為問題。透過暫時停用約束，可以順利進行操作。

停用外部鍵約束的方法

SET FOREIGN_KEY_CHECKS = 0;

-- 執行大量資料的插入或刪除
DELETE FROM parent;

SET FOREIGN_KEY_CHECKS = 1;

注意:
停用約束可能會導致參照完整性喪失，因此操作後務必重新啟用。

6. 外部鍵的最佳實務

外部鍵約束在 MySQL 中確保資料庫完整性是非常有用的功能，但如果設計和使用不當，可能會導致效能降低或營運問題。本節將介紹有效利用外部鍵的最佳實務。

1. 辨識應使用外部鍵的場景

外部鍵約束並非所有資料表之間的關係都必須使用。在使用時，請考慮以下類型的場景。

• 建議使用的情況:
• 資料完整性重要的情況（例如：訂單資料表與客戶資料表）。
• 為了讓其他開發者或團隊不會誤解參照規則，而明確顯示關係性的情況。
• 應避免使用的情況:
• 進行高頻率大規模資料插入・刪除的情況（外部鍵約束可能影響效能）。
• 在應用程式程式碼中管理資料完整性的情況。

2. 正確設定欄位的資料型態和屬性

使用外部鍵約束時，父資料表和子資料表的欄位資料型態和屬性必須一致，這是不可或缺的。

建議設定

• 資料型態必須一致（例如：兩者皆為INT）。
• 屬性也必須一致（例如：UNSIGNED、NOT NULL）。

不一致的範例與修正

-- 修正前
CREATE TABLE parent (
    id INT PRIMARY KEY
);

CREATE TABLE child (
    parent_id INT UNSIGNED,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

-- 修正後
CREATE TABLE parent (
    id INT UNSIGNED PRIMARY KEY
);

CREATE TABLE child (
    parent_id INT UNSIGNED,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

3. 選擇適當的儲存引擎

在 MySQL 中，要利用外部鍵約束，必須使用支援的儲存引擎。

• 建議引擎: InnoDB
• 注意事項: MyISAM 等儲存引擎不支援外部鍵約束。

CREATE TABLE example_table (
    id INT PRIMARY KEY
) ENGINE=InnoDB;

4. 謹慎選擇外部鍵選項

設定外部鍵約束時，適當選擇ON DELETE或ON UPDATE選項，即可防止意外的資料刪除或更新。

建議的設定範例

• 需要連動刪除資料的情況: ON DELETE CASCADE
• 想要保留參照的情況: ON DELETE SET NULL
• 想要防止誤操作的情況: ON DELETE RESTRICT

FOREIGN KEY (category_id) REFERENCES categories(id)
ON DELETE CASCADE ON UPDATE CASCADE;

5. 刪除外部鍵約束時的注意事項

當外部鍵約束不再需要時，也可以刪除。然而，刪除約束會影響資料完整性，因此必須慎重進行。

外部鍵約束的刪除範例

ALTER TABLE child_table
DROP FOREIGN KEY fk_name;

6. 效能最佳化

外部鍵約束雖然保證參照完整性，但會在插入或刪除時產生額外的負荷。請考慮以下要點來最佳化效能。

活用索引

指定為外部鍵的欄位建立索引，即可提升查詢效能。在 MySQL 中，設定外部鍵約束時會自動建立索引，但請明確確認。

大量資料操作時的約束無效化

插入或刪除大量資料時，建議暫時無效化外部鍵約束。

SET FOREIGN_KEY_CHECKS = 0;
-- 大量資料操作
SET FOREIGN_KEY_CHECKS = 1;

7. 文件與團隊間的分享

設定外部鍵約束時，將其意圖或設計理念在團隊內分享是非常重要的。如果關係性複雜，活用 ER 圖（實體關係圖）會更好。

7. 常見問題 (FAQ)

在這裡，我們彙整了有關 MySQL 外部鍵的常見問題及其解答。從初學者的疑問到實務上的挑戰，都廣泛涵蓋。

Q1. 設定外部鍵約束，有什麼優點嗎？

A1.透過設定外部鍵約束，有以下優點：

• 資料的完整性保證：防止插入或更新不存在的參照資料。
• 資料庫設計更明確：表格間的關係性變得視覺上更容易理解。
• 減輕應用程式程式碼的負擔：完整性檢查可由資料庫端自動處理，因此程式碼會更簡潔。

Q2. 設定外部鍵約束會影響效能嗎？

A2.是的，外部鍵約束的完整性檢查可能會在 INSERT、UPDATE、DELETE 操作時造成額外的負荷。不過，可以透過以下方式將影響降到最低。

• 為設定為外部鍵的欄位建立索引。
• 大量資料操作時，一時停用約束。
• 僅在必要的情況下使用外部鍵約束。

Q3. 外部鍵約束可以在所有儲存引擎中使用嗎？

A3.不行，在 MySQL 中，外部鍵約束主要由 InnoDB 儲存引擎支援。其他引擎（例如: MyISAM）不支援外部鍵約束。建立表格時，請如以下方式指定 InnoDB。

CREATE TABLE table_name (
    id INT PRIMARY KEY
) ENGINE=InnoDB;

Q4. 設定外部鍵約束時，父表格和子表格的資料型態需要一致嗎？

A4.是的，需要一致。如果父表格和子表格對應欄位的資料型態及屬性（例如: UNSIGNED、NOT NULL 等）不一致，設定外部鍵約束時會發生錯誤。

Q5. 當外部鍵約束造成錯誤時，如何進行疑難排解？

A5.當發生外部鍵約束錯誤時，請確認以下事項。

1. 資料型態的一致性：請確認父表格和子表格的欄位資料型態。
2. 父資料的存在確認：請確認要插入子表格的資料是否存在於父表格中。
3. 儲存引擎：請確認兩個表格都使用 InnoDB。
4. 外部鍵的有效性：請使用以下指令一時停用外部鍵約束，確認是否能進行操作：

   SET FOREIGN_KEY_CHECKS = 0;

Q6. 可以不刪除外部鍵約束而一時停用嗎？

A6.是的，可以一時停用外部鍵約束。請使用以下 SQL 指令：

SET FOREIGN_KEY_CHECKS = 0;
-- 執行必要的操作
SET FOREIGN_KEY_CHECKS = 1;

這種方法在進行大量資料操作時很方便，但可能會導致參照完整性喪失，因此請謹慎使用。

Q7. 如果需要從父表格刪除大量資料，該如何處理？

A7.請參考以下步驟。

1. 一時停用外部鍵約束。

   SET FOREIGN_KEY_CHECKS = 0;

2. 執行必要的刪除操作。

   DELETE FROM parent_table;

3. 再次啟用外部鍵約束。

   SET FOREIGN_KEY_CHECKS = 1;

Q8. 請教我如何刪除外部鍵約束。

A8.可以使用以下指令刪除外部鍵約束。

ALTER TABLE child_table
DROP FOREIGN KEY fk_name;

fk_name 是外部鍵約束的名稱。外部鍵名稱可以使用 SHOW CREATE TABLE table_name; 來確認。

8. 總結

這篇文章中，我們廣泛說明了 MySQL 的外部鍵約束（Foreign Key），從基本概念到設定方法、疑難排解、最佳實務，以及 FAQ。以下回顧這篇文章的重點。

外部鍵約束的基本概念

• 外部鍵約束是用來定義資料表之間的關係性，並保證參照完整性的重要功能。
• 主要用於管理父資料表與子資料表的關係，以維持資料的一致性。

外部鍵約束的設定與運作

• 可以在建立資料表時，或是對現有資料表設定外部鍵約束。
• 利用 ON DELETE 或 ON UPDATE 的選項，可以靈活控制父資料表的資料操作行為。
• 設定外部鍵約束時，請謹慎選擇欄位的資料型態及儲存引擎（InnoDB）。

常見問題與解決方法

• 資料型態不一致或缺少父資料等典型的錯誤，可以透過設計階段的注意及適當設定來避免。
• 如果外部鍵約束造成問題，可以暫時停用來進行操作，以提升效率。

最佳實務

• 僅在必要時使用外部鍵約束，避免過度設定。
• 透過索引的活用及適當的 ON DELETE / ON UPDATE 選項選擇，來最大化效能。
• 在團隊間分享外部鍵的設計意圖並記錄文件也很重要。

後續步驟

請參考這篇文章，試著執行以下步驟。

1. 建立測試用資料庫，設定外部鍵約束並確認其運作。
2. 在包含大量資料的環境中測量效能，並視需要進行調整。
3. 在實際專案中應用外部鍵約束，目標是確保資料的完整性設計。

適當活用外部鍵約束，可以讓資料庫設計更為堅固，並提升長期的運作效率。希望這篇文章能對各位使用 MySQL 有所幫助。