MySQL 課程 – 資料庫設計與正規化

主題：鍵值（主鍵、外鍵、候選鍵）

---

簡介

在關聯式資料庫的設計過程中，**鍵值（Key）**是決定資料完整性與查詢效能的核心要素。沒有正確的鍵值規劃，資料庫很容易產生重複資料、孤兒記錄或是更新異常，最終會影響系統的穩定性與維護成本。
本篇文章將以 MySQL 為例，深入說明 主鍵（Primary Key）、外鍵（Foreign Key） 與 候選鍵（Candidate Key） 的概念、使用方式以及實務上的最佳做法，幫助從初學者到中階開發者都能建立「正規化」且易於擴充的資料模型。

---

核心概念

1. 主鍵（Primary Key）

• 唯一性：每一筆資料在主鍵欄位上必須是唯一的，不能有重複值。
• 不可為 NULL：MySQL 會自動把主鍵欄位設為 NOT NULL。
• 單一或複合鍵：可以只使用一個欄位，也可以使用多個欄位組成 複合主鍵（Composite Primary Key）。

為什麼需要主鍵？

• 快速定位：MySQL 會自動在主鍵上建立 聚簇索引（Clustered Index），搜尋效率大幅提升。
• 資料完整性：防止同一筆資料被插入多次。

範例 1：簡單主鍵

CREATE TABLE `employees` (
    `emp_id` INT UNSIGNED AUTO_INCREMENT,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name`  VARCHAR(50) NOT NULL,
    `email`      VARCHAR(100) UNIQUE,
    PRIMARY KEY (`emp_id`)               -- ← 主鍵設定
) ENGINE=InnoDB;

註解：emp_id 使用 AUTO_INCREMENT，MySQL 會自動產生唯一值，且自動成為主鍵。

範例 2：複合主鍵

CREATE TABLE `order_items` (
    `order_id`   INT NOT NULL,
    `product_id` INT NOT NULL,
    `quantity`   INT DEFAULT 1,
    PRIMARY KEY (`order_id`, `product_id`)   -- ← 複合主鍵
) ENGINE=InnoDB;

註解：同一筆訂單內，同一商品只能出現一次。order_id + product_id 的組合保證唯一性。

---

2. 外鍵（Foreign Key）

• 參照完整性（Referential Integrity）：外鍵用來建立兩張表之間的關聯，保證子表（Child）中的值必須在父表（Parent）的主鍵或唯一鍵中存在。
• 行為設定：可透過 ON DELETE、ON UPDATE 來定義當父表資料變動時子表的處理方式（CASCADE、SET NULL、RESTRICT 等）。

為什麼需要外鍵？

• 防止 孤兒資料（Orphan Record），即子表的外鍵指向一筆已不存在的父表資料。
• 確保 資料一致性，例如刪除客戶時，同時刪除或保留其訂單。

範例 3：一對多關係（客戶 ↔ 訂單）

CREATE TABLE `customers` (
    `customer_id` INT UNSIGNED AUTO_INCREMENT,
    `name`        VARCHAR(100) NOT NULL,
    PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB;

CREATE TABLE `orders` (
    `order_id`    INT UNSIGNED AUTO_INCREMENT,
    `order_date`  DATE NOT NULL,
    `customer_id` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`order_id`),
    CONSTRAINT `fk_orders_customers`
        FOREIGN KEY (`customer_id`)          -- ← 外鍵設定
        REFERENCES `customers`(`customer_id`)
        ON DELETE CASCADE                     -- 刪除客戶時，同步刪除其訂單
        ON UPDATE RESTRICT
) ENGINE=InnoDB;

註解：ON DELETE CASCADE 讓資料庫自動維護關聯，避免手動寫額外的刪除程式碼。

範例 4：多對多關係（商品 ↔ 訂單）

CREATE TABLE `products` (
    `product_id` INT UNSIGNED AUTO_INCREMENT,
    `name`       VARCHAR(150) NOT NULL,
    `price`      DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (`product_id`)
) ENGINE=InnoDB;

-- 中介表，使用兩個外鍵分別指向 products & orders
CREATE TABLE `order_items` (
    `order_id`   INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `quantity`   INT DEFAULT 1,
    PRIMARY KEY (`order_id`, `product_id`),
    CONSTRAINT `fk_oi_order`
        FOREIGN KEY (`order_id`) REFERENCES `orders`(`order_id`)
        ON DELETE CASCADE,
    CONSTRAINT `fk_oi_product`
        FOREIGN KEY (`product_id`) REFERENCES `products`(`product_id`)
        ON DELETE RESTRICT
) ENGINE=InnoDB;

註解：order_items 同時扮演 複合主鍵 與 兩個外鍵 的角色，完整描述「一筆訂單可以包含多個商品」的關係。

---

3. 候選鍵（Candidate Key）

• 定義：在一張表中，可作為主鍵的欄位或欄位組合，每個候選鍵都具備唯一性且不可為 NULL。
• 主鍵與唯一鍵的關係：從候選鍵中挑選一組作為 主鍵（Primary Key），其餘的則可以宣告為 唯一鍵（Unique Key）。

為什麼要了解候選鍵？

• 在設計階段，可能會有多個欄位同時具備唯一性，選擇最適合作為主鍵的欄位可以降低 索引開銷、提升 查詢效能。
• 若未明確定義唯一鍵，資料庫仍可能允許重複資料，導致 正規化 失敗。

範例 5：多個候選鍵

CREATE TABLE `students` (
    `student_id`   CHAR(9)        NOT NULL,   -- 例：A12345678
    `national_id`  CHAR(10)       NOT NULL,   -- 身分證號
    `email`        VARCHAR(100)   NOT NULL,
    `name`         VARCHAR(50)    NOT NULL,
    PRIMARY KEY (`student_id`),                 -- 以 student_id 為主鍵
    UNIQUE KEY `uk_national_id` (`national_id`),-- 另一候選鍵
    UNIQUE KEY `uk_email` (`email`)             -- 再一候選鍵
) ENGINE=InnoDB;

註解：student_id、national_id、email 都是 候選鍵，其中 student_id 被選為主鍵，其餘兩個則以 唯一鍵 方式保護唯一性。

---

常見陷阱與最佳實踐

常見陷阱	可能的問題	建議的最佳實踐
使用自然鍵（Natural Key）作為主鍵，但未加唯一索引	資料變更時需同時更新主鍵，導致大量連鎖更新	若自然鍵頻繁變動，建議改用 代理鍵（Surrogate Key）（如 AUTO_INCREMENT）
外鍵未加索引	刪除/更新父表時，MySQL 必須全表掃描子表，效能急遽下降	為每個外鍵欄位 建立索引（MySQL 5.6+ 會自動建立，但仍建議手動確認）
複合主鍵過長	索引大小過大，導致磁碟與記憶體使用量飆升	僅在必須時使用複合鍵，否則使用單一代理鍵
忘記設定 ON DELETE/ON UPDATE	刪除父表資料會產生孤兒記錄，或更新失敗拋出錯誤	明確指定行為，最常見是 ON DELETE CASCADE 或 ON DELETE RESTRICT
把唯一鍵當作主鍵，但未考慮 NULL	NULL 不被視為相等，會破壞唯一性	若欄位允許 NULL，請改用 NOT NULL 或另建代理鍵

其他實務建議

1. 命名規則：外鍵約束名稱建議使用 fk_<子表>_<父表>，唯一鍵使用 uk_<表>_<欄位>，主鍵則直接使用 PRIMARY KEY。
2. 使用 InnoDB 引擎：只有 InnoDB 才支援外鍵參照完整性，MyISAM 會忽略外鍵定義。
3. 避免過度正規化：在極端查詢需求下，過度拆表會導致大量 JOIN，可適度使用 冗餘欄位（Denormalization）提升讀取效能。
4. 檢查資料一致性：部署前可使用 CHECK TABLE 或 mysqldump --single-transaction 來驗證外鍵約束是否正確。

---

實際應用場景

場景 1：電子商務平台的訂單系統

• 主鍵：orders.order_id（代理鍵）確保每筆訂單唯一。
• 外鍵：order_items.order_id 參照 orders.order_id，order_items.product_id 參照 products.product_id，保證 訂單明細 永遠與有效的訂單與商品對應。
• 候選鍵：orders.order_number（客戶可見的訂單編號）設為唯一鍵，防止客戶自行重複輸入。

場景 2：人事系統的員工與部門關係

表格	主鍵	外鍵	候選鍵
departments	dept_id	-	dept_code（唯一）
employees	emp_id	dept_id → departments.dept_id	employee_number（唯一）

• 透過 外鍵 讓每位員工必須屬於已存在的部門，且在部門刪除時使用 ON DELETE SET NULL，保留員工資料但解除部門關聯。

場景 3：學校系統的學生與課程選修

• 複合主鍵 enrollments(student_id, course_id) 確保同一學生不會重複選修同一課程。
• 外鍵 分別指向 students.student_id、courses.course_id，維護 參照完整性。
• 候選鍵 enrollment_id（代理鍵）可作為報表或 API 回傳的唯一識別碼，提升前端開發便利性。

---

總結

• 主鍵 為資料表的唯一識別，建議使用不可變且短小的 代理鍵（如 INT AUTO_INCREMENT），或在確定不會變動的情況下使用自然鍵。
• 外鍵 用於建立表與表之間的關聯，務必配合適當的 索引 與 ON DELETE / ON UPDATE 行為，才能保證資料的一致性與效能。
• 候選鍵 是所有可能成為主鍵的欄位集合，透過挑選最合適的鍵作為主鍵，其餘則以 唯一鍵 形式保護資料完整性。

掌握這三種鍵值的概念與最佳實踐，能讓你在設計 MySQL 資料庫時，避免資料異常、提升查詢效能、減少維護成本。在日常開發中，別忘了：

1. 先設計鍵值再建表 → 先思考唯一性與關聯性。
2. 使用 InnoDB → 確保外鍵約束生效。
3. 測試與檢查 → 部署前跑 SHOW CREATE TABLE、EXPLAIN 與資料一致性檢查。

祝你在 MySQL 的資料庫設計旅程中，能寫出乾淨、正規化且高效能的資料模型！ 🎉