MySQL 外鍵約束完整指南：原理、建立與級聯操作實戰

MySQL 外鍵約束完整指南：原理、建立與級聯操作實戰

外鍵是關聯式資料庫維護資料完整性的核心機制。本文詳細解析 MySQL 外鍵的使用條件、語法選項、級聯行為，並透過「大哥/小弟」實戰案例展示約束效果與異常處理。

一、外鍵使用前提條件

1. 儲存引擎必須為 InnoDB：MyISAM 不支援外鍵（舊版完全不支援，新版亦無計畫支援）。
2. 外鍵欄位需建立索引：MySQL 4.1.2+ 版本建立外鍵時會自動創建索引，舊版需手動建立。
3. 資料型別必須相容：如 INT 對應 TINYINT 可行，但 INT 對應 CHAR 會報錯。

二、外鍵定義語法詳解

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

• CONSTRAINT symbol：約束名稱，省略時 MySQL 自動生成。
• ON DELETE / ON UPDATE：定義父表記錄異動時，子表的對應行為。
  • RESTRICT（預設）：阻止父表刪除/更新，若子表有關聯資料則報錯。
  • CASCADE：級聯操作，父表異動自動同步到子表（刪除父記錄 → 刪除子記錄；更新父鍵值 → 更新子鍵值）。
  • SET NULL：子表外鍵欄位設為 NULL（前提是欄位允許 NULL）。
  • SET DEFAULT：設為欄位預設值（MySQL 目前解析但忽略，實際行為同 RESTRICT）。
  • NO ACTION：同 RESTRICT，標準 SQL 語法別名。

三、實戰案例：大哥與小弟

1. 建立主表（大哥）與子表（小弟）

CREATE TABLE `dage` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(32) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `xiaodi` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `dage_id` INT(11) DEFAULT NULL,
  `name` VARCHAR(32) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `dage_id` (`dage_id`),
  CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 測試預設行為（RESTRICT）

-- 新增大哥
INSERT INTO dage(name) VALUES('銅鑼灣');  -- id=1
-- 新增小弟
INSERT INTO xiaodi(dage_id, name) VALUES(1, '銅鑼灣_小弟A');

-- 嘗試刪除大哥 → 失敗
DELETE FROM dage WHERE id=1;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails

-- 嘗試新增不存在的大哥 ID → 失敗
INSERT INTO xiaodi(dage_id, name) VALUES(2, '旺角_小弟A');
-- ERROR 1452 (23000): Cannot add or update a child row

3. 修改為級聯刪除/更新

-- 刪除舊外鍵
ALTER TABLE xiaodi DROP FOREIGN KEY xiaodi_ibfk_1;
-- 重新加入 CASCADE
ALTER TABLE xiaodi ADD FOREIGN KEY (dage_id) REFERENCES dage(id)
  ON DELETE CASCADE ON UPDATE CASCADE;

4. 驗證級聯效果

DELETE FROM dage WHERE id=1;
-- 成功，對應小弟記錄自動刪除
SELECT * FROM xiaodi;  -- Empty set

四、常見問題與最佳實踐

• 效能考量：外鍵會增加寫入開銷（需檢查父表），高併發寫入場景可考慮應用層維護完整性，資料庫層面僅建立索引不建外鍵。
• 遞迴級聯風險：多層級聯刪除可能導致大量資料連鎖刪除，謹慎使用 CASCADE。
• 備份/還原順序：還原資料時需先建立父表再建子表，或暫時關閉外鍵檢查 SET FOREIGN_KEY_CHECKS=0;。
• 線上加欄位/改型別：涉及外鍵欄位的 DDL 操作需先刪除外鍵，操作完成後重建。

結語：外鍵是資料完整性的守門員，善用 ON DELETE/UPDATE CASCADE 可大幅簡化應用層邏輯，但需權衡效能與維護複雜度。建議在設計階段明確界定業務語義，選擇適合的約束策略。