MySQL 課程 – 外鍵與約束 (Constraint)

主題：PRIMARY KEY 與 FOREIGN KEY

---

簡介

在關聯式資料庫中，資料的完整性與關聯性是系統能否正確運作的根本。
PRIMARY KEY（主鍵）與 FOREIGN KEY（外鍵）正是 MySQL 用來保證這兩項需求的兩大核心機制。

• 主鍵 為每一筆資料賦予唯一的識別碼，確保同一張表內不會出現重複的記錄。
• 外鍵 則在不同資料表之間建立「參考」關係，使得資料在插入、更新或刪除時必須符合父子表的約束規則。

掌握這兩者的概念與正確使用方式，不僅能避免資料不一致的問題，還能讓查詢更具可讀性、維護成本更低。以下將從概念說明、實作範例、常見陷阱與最佳實踐，帶你一步步深入了解 PRIMARY KEY 與 FOREIGN KEY 在 MySQL 中的運作原理與實務應用。

---

核心概念

1. PRIMARY KEY（主鍵）

• 唯一性：主鍵欄位的每一個值必須唯一，不能重複。
• 不可為 NULL：MySQL 會自動在主鍵欄位加上 NOT NULL 限制。
• 單欄位或複合鍵：可以是單一欄位，也可以由多個欄位組成（稱為「複合主鍵」）。
• 自動索引：建立主鍵時，MySQL 會自動為該欄位建立唯一索引（PRIMARY KEY 索引），提升搜尋效能。

範例 1：單欄位主鍵

CREATE TABLE employee (
    emp_id INT AUTO_INCREMENT,      -- 自動遞增，作為唯一識別碼
    emp_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    PRIMARY KEY (emp_id)            -- 設定 emp_id 為主鍵
);

範例 2：複合主鍵

CREATE TABLE order_item (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    PRIMARY KEY (order_id, product_id)   -- 同時以 order_id 與 product_id 為唯一鍵
);

小技巧：如果資料表已經有唯一索引且不需要自動遞增，亦可直接使用 UNIQUE 取代 PRIMARY KEY，但在設計上仍建議保留單一的主鍵欄位，以利日後的關聯。

---

2. FOREIGN KEY（外鍵）

• 參考父表：外鍵欄位的值必須在父表（被參考的表）中已存在，才能插入或更新。
• 參照完整性（Referential Integrity）：MySQL 會在資料變更時自動檢查外鍵約束，避免「孤兒」資料（沒有對應父資料）出現。
• 行為設定：可以透過 ON DELETE、ON UPDATE 來指定當父表的資料被刪除或更新時，子表應如何處理。常見的選項有：
  • CASCADE：同步刪除或更新子表資料。
  • SET NULL：將子表外鍵欄位設為 NULL（前提是欄位允許 NULL）。
  • RESTRICT（或 NO ACTION）：阻止父表的刪除或更新動作。
  • SET DEFAULT：將外鍵欄位設為預設值（MySQL 8.0 以上支援）。

範例 3：單欄位外鍵 + ON DELETE/ON UPDATE

CREATE TABLE department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(30) NOT NULL
);

CREATE TABLE employee (
    emp_id INT AUTO_INCREMENT,
    emp_name VARCHAR(50) NOT NULL,
    dept_id INT,
    PRIMARY KEY (emp_id),
    CONSTRAINT fk_emp_dept
        FOREIGN KEY (dept_id)          -- 參考 department.dept_id
        REFERENCES department(dept_id)
        ON DELETE SET NULL             -- 部門被刪除時，員工的 dept_id 變為 NULL
        ON UPDATE CASCADE;             -- 部門代號變更時，員工自動同步
);

範例 4：複合外鍵（對應複合主鍵）

CREATE TABLE purchase_order (
    order_id INT PRIMARY KEY,
    order_date DATE NOT NULL
);

CREATE TABLE order_item (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_order_item_order
        FOREIGN KEY (order_id)               -- 只參考 order_id
        REFERENCES purchase_order(order_id)
        ON DELETE CASCADE;                   -- 刪除訂單時，同時刪除所有明細
);

範例 5：自訂外鍵名稱與多外鍵

CREATE TABLE project (
    proj_id INT PRIMARY KEY,
    proj_name VARCHAR(100) NOT NULL
);

CREATE TABLE task (
    task_id INT PRIMARY KEY,
    proj_id INT NOT NULL,
    assignee_id INT,
    CONSTRAINT fk_task_project
        FOREIGN KEY (proj_id) REFERENCES project(proj_id)
        ON DELETE RESTRICT,
    CONSTRAINT fk_task_employee
        FOREIGN KEY (assignee_id) REFERENCES employee(emp_id)
        ON DELETE SET NULL
);

注意：外鍵只能參考 已建立索引（主鍵或唯一鍵）的欄位；若父表的參考欄位不是主鍵或唯一鍵，必須先手動加上 UNIQUE 索引。

---

常見陷阱與最佳實踐

陷阱	說明	解決或最佳做法
外鍵欄位類型不一致	父表與子表的欄位型別（長度、簽名）必須完全相同。	建議使用 INT UNSIGNED、BIGINT 等明確定義，避免自動轉型錯誤。
忘記加索引	大量插入/更新外鍵資料時，若父表缺少索引會導致鎖表或效能下降。	為外鍵欄位自行建立索引（INDEX (col)），或直接使用 FOREIGN KEY 時 MySQL 會自動建立。
循環依賴	A 表的外鍵參考 B 表，同時 B 表又參考 A 表，會造成建立表時失敗。	可使用 延遲建立外鍵（先建立表，之後再 ALTER TABLE 加外鍵），或重新設計資料模型。
ON DELETE/ON UPDATE 行為不當	使用 CASCADE 時若層級過深，刪除/更新可能波及大量資料，導致意外資料遺失。	先評估業務需求，盡量使用 RESTRICT 或 SET NULL，並在關鍵操作前加上事前備份。
外鍵欄位允許 NULL	若外鍵欄位允許 NULL，插入 NULL 時不會檢查參考完整性，可能造成資料孤島。	僅在確實需要「可無關聯」的情況下才允許 NULL，否則加上 NOT NULL。

最佳實踐清單

1. 統一命名規則
   • 主鍵名稱常用 PK_<table>；外鍵名稱用 FK_<child>_<parent>_<col>，方便日後維護與排錯。
2. 使用 InnoDB 引擎
   • 只有 InnoDB 支援外鍵約束，若是 MyISAM 等引擎則不會生效。
3. 保持欄位型別一致
   • 例如父表 INT UNSIGNED，子表也必須是 INT UNSIGNED，包括長度與 ZEROFILL（若有）。
4. 適度使用複合鍵
   • 複合主鍵適合自然鍵（如 order_id + product_id），但過於複雜會影響 JOIN 效能。
5. 定期檢查參照完整性
   • 可使用 CHECK TABLE <table> EXTENDED 或 SELECT ... LEFT JOIN 來驗證是否出現孤兒資料。

---

實際應用場景

1. 電子商務系統的訂單管理

• orders 表儲存訂單基本資訊（order_id 為主鍵）。
• order_items 表以 (order_id, product_id) 為複合主鍵，並以 order_id 建立外鍵指向 orders，確保每筆明細必屬於既有訂單。
• products 表的 product_id 再被 order_items 參考，保證商品必須已存在於商品目錄。

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_item_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,
    CONSTRAINT fk_item_product
        FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
);

2. 人事系統的部門與員工關聯

• 部門 為父表，員工 為子表。
• 使用 ON DELETE SET NULL，當部門被刪除時，員工仍保留，只是部門欄位變為 NULL，避免意外刪除員工資料。

3. 多層級分類（樹狀結構）

若要建立類別的父子關係，可在同一張表內使用自參照外鍵：

CREATE TABLE category (
    cat_id INT PRIMARY KEY AUTO_INCREMENT,
    cat_name VARCHAR(50) NOT NULL,
    parent_id INT,
    CONSTRAINT fk_category_parent
        FOREIGN KEY (parent_id) REFERENCES category(cat_id)
        ON DELETE SET NULL
);

這樣即能保證每個子類別的 parent_id 必須是已存在的類別，且刪除父類別時子類別不會被連帶刪除。

---

總結

• PRIMARY KEY 為資料表提供唯一的識別，並自動建立唯一索引，確保每筆資料不可重複且不可為 NULL。
• FOREIGN KEY 則在不同資料表之間建立參照關係，透過 ON DELETE、ON UPDATE 等行為設定，維護 參照完整性，防止孤兒資料產生。
• 正確的型別一致、索引建立與命名規則，是避免常見陷阱的關鍵；同時，根據業務需求選擇適當的 ON DELETE/ON UPDATE 行為，可提升系統的穩定性與可維護性。
• 在實務上，無論是電商訂單、企業人事或樹狀分類，主鍵與外鍵的設計都是資料模型的基石。熟練掌握它們的使用方式，將讓你的 MySQL 資料庫在效能、正確性與擴充性上都有更佳表現。

實務建議：在每次資料模型變更前，先在測試環境中驗證外鍵行為，並搭配自動化測試腳本，確保資料完整性不因程式碼更新而受損。

祝你在 MySQL 的學習與開發旅程中，玩得開心、寫得順手！ 🚀