MySQL 外鍵與參照完整性：關係型建模的基石

外鍵 是關係型資料庫區別於檔案系統、NoSQL 的核心特徵之一。它在資料庫層面強制執行 **參照完整性**，確保「子表記錄必須指向存在的父表記錄」。本文系統講解概念、語法、行為模式、效能影響與架構選型建議。

核心概念：父表、子表、參照完整性

-- 父表 (被參照表)
CREATE TABLE categories (
  category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB;

-- 子表 (參照表)
CREATE TABLE articles (
  article_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  category_id TINYINT UNSIGNED NOT NULL,
  title VARCHAR(200) NOT NULL,
  -- 定義外鍵
  CONSTRAINT fk_article_category
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

• 父表：擁有被參照的主鍵/唯一鍵 (`categories.category_id`)。
• 子表：擁有外鍵欄位 (`articles.category_id`)，值必須存在於父表主鍵中 (或為 NULL，若允許)。
• 參照完整性：資料庫拒絕任何導致「孤兒記錄」的操作 (插入不存在的 category_id、刪除被參照的 category、修改父表 PK 導致子表失效)。

五種參照動作 (ON DELETE / ON UPDATE)

建立外鍵的四個硬性條件

1. 引擎必須是 InnoDB：MyISAM、Memory 等不支援外鍵 (語法解析但忽略執行)。
2. 父表被參照列必須有索引：主鍵、唯一索引、或普通索引 (MySQL 允許參照非唯一索引，但語義通常要求唯一)。
3. 子表外鍵列必須有索引：MySQL 4.1+ 自動建立，舊版需手動 `ADD INDEX`。
4. 資料類型、長度、簽名、字符集、排序規則**完全一致**：
   • `INT UNSIGNED` ≠ `INT` (簽名不同)
   • `VARCHAR(50) utf8mb4` ≠ `VARCHAR(50) latin1` (字符集不同)
   • `CHAR(10)` ≠ `VARCHAR(10)` (類型不同)

外鍵語法大全

-- 建表時定義
CREATE TABLE child (
  id INT PRIMARY KEY,
  parent_id INT,
  CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ALTER TABLE 新增 (需先有索引)
ALTER TABLE child ADD INDEX idx_parent_id (parent_id);
ALTER TABLE child ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE SET NULL;

-- 刪除外鍵
ALTER TABLE child DROP FOREIGN KEY fk_child_parent;
-- 注意：DROP FOREIGN KEY 只刪除約束，**不自動刪除索引**，需額外 `DROP INDEX idx_parent_id`。

-- 查看外鍵資訊
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_SCHEMA = 'my_db';
SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL AND TABLE_SCHEMA = 'my_db';

效能與鎖影響 (必讀)

• 插入/更新子表：需檢查父表記錄存在 → **共享鎖 (S-lock)** 父表主鍵記錄，高併發下可能阻塞。
• 刪除/更新父表：需掃描子表匹配記錄 → **意向排他鎖 (IX) + 記錄鎖 (X)** 子表對應行，若無索引會鎖全表 (災難)。
• CASCADE 級聯：遞歸加鎖，深度層級多、子表大時極易死鎖、長鎖、主從延遲。
• 外鍵檢查開關：`SET FOREIGN_KEY_CHECKS = 0/1;` (Session/Global)。**僅用於大量導入/遷移**，業務運行時**嚴禁關閉**。

架構選型：外鍵 vs 應用層維護

現代最佳實踐建議

1. 核心主數據 (用戶、商品、帳戶、字典表)：**建立外鍵 + RESTRICT**，資料庫層面兜底，應用層友好提示。
2. 明確從屬、生命週期綁定 (訂單-項、文章-評論、父子分類)：**外鍵 + CASCADE**，但**必須**配合軟刪除 (`deleted_at`)、審計日誌、嚴格權限控制。
3. 高併發寫入、微服務、跨庫、最終一致可接受：**不建外鍵**，應用層用「先查父表存在性、再寫子表」、補償事務 (Saga)、異步校驗任務保證一致性。
4. 所有外鍵欄位必須顯式建立索引** (MySQL 8.0+ 自動)，並定期 `ANALYZE TABLE` 更新統計。
5. 禁止在生產環境關閉 `FOREIGN_KEY_CHECKS`** 除非經過變更審批、備份驗證、維護窗口。

-- 1. 缺少索引的外鍵列 (子表)
SELECT kcu.TABLE_NAME, kcu.COLUMN_NAME, kcu.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
LEFT JOIN INFORMATION_SCHEMA.STATISTICS s
  ON s.TABLE_SCHEMA = kcu.TABLE_SCHEMA
  AND s.TABLE_NAME = kcu.TABLE_NAME
  AND s.COLUMN_NAME = kcu.COLUMN_NAME
  AND s.SEQ_IN_INDEX = 1
WHERE kcu.REFERENCED_TABLE_NAME IS NOT NULL
  AND kcu.TABLE_SCHEMA = 'my_db'
  AND s.INDEX_NAME IS NULL;

-- 2. CASCADE 深度超過 3 層的鏈路 (風險預警)
WITH RECURSIVE fk_chain AS (
  SELECT kcu.TABLE_NAME, kcu.COLUMN_NAME, kcu.REFERENCED_TABLE_NAME, 1 AS depth
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
  WHERE kcu.REFERENCED_TABLE_NAME IS NOT NULL AND kcu.TABLE_SCHEMA = 'my_db'
  UNION ALL
  SELECT kcu.TABLE_NAME, kcu.COLUMN_NAME, kcu.REFERENCED_TABLE_NAME, fc.depth + 1
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
  JOIN fk_chain fc ON fc.REFERENCED_TABLE_NAME = kcu.TABLE_NAME
  WHERE kcu.TABLE_SCHEMA = 'my_db'
)
SELECT * FROM fk_chain WHERE depth > 3;