您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 外鍵約束完整指南：InnoDB 級聯操作與資料完整性實戰

編輯：MySQL 資料庫相關知識

為什麼需要外鍵？

應用層維護關聯完整性（如：刪除部落格文章時同步刪除留言）程式碼複雜、易出錯、效能差。資料庫層級的外鍵約束（FOREIGN KEY）能自動執行級聯動作，確保資料一致性，簡化應用邏輯。

外鍵約束動作類型

動作	觸發條件	子表行為	適用場景
`CASCADE`	父表 UPDATE/DELETE	同步 UPDATE/DELETE 關聯行	訂單-明細、文章-留言、部門-員工
`SET NULL`	父表 UPDATE/DELETE	外鍵欄位設為 NULL（需允許 NULL）	可選關聯、軟刪除保留子記錄
`NO ACTION`	父表 UPDATE/DELETE	拒絕操作，報錯（標準 SQL 行為）	嚴格保護父記錄
`RESTRICT`	父表 UPDATE/DELETE	拒絕操作，報錯（MySQL 預設）	同 NO ACTION，檢查時機更早
`SET DEFAULT`	父表 UPDATE/DELETE	外鍵欄位設為 DEFAULT 值	極少用，需有 DEFAULT 值

MyISAM vs InnoDB：外鍵支援差異

MyISAM：不支援 外鍵約束（語法解析但忽略），需應用層維護
InnoDB：完整支援外鍵、事務、行鎖、崩潰恢復
效能代價：InnoDB 寫入較慢（約 20-30%），但換取資料安全與一致性
現代建議：核心業務表一律 InnoDB + 外鍵；唯讀/日誌/緩存表可用 MyISAM

實戰：部落格文章與留言關聯模型

1. 建立父表：blogs

CREATE TABLE `blogs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` TEXT NOT NULL,
    `content` TEXT NOT NULL,
    `author` VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 建立子表：comments（含外鍵）

CREATE TABLE `comments` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `blog_id` INT UNSIGNED NOT NULL,
    `comment` TEXT NOT NULL,
    `author` VARCHAR(45) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_blog_id` (`blog_id`),
    CONSTRAINT `fk_comments_blog` 
        FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

關鍵點解析

INDEX idx_blog_id：外鍵欄位必須有索引，InnoDB 會自動建立若未指定
CONSTRAINT fk_comments_blog：命名約束，便於管理與除錯
ON UPDATE CASCADE：父表 id 變更時，子表 blog_id 自動同步
ON DELETE CASCADE：刪除文章時，關聯留言自動刪除

驗證級聯操作

-- 插入測試資料
INSERT INTO blogs (title, content, author) VALUES ('第一篇', '內容...', 'Ian');
INSERT INTO comments (blog_id, comment, author) VALUES (1, '留言1', 'Susan'), (1, '留言2', 'Rose');

-- 更新父表主鍵（觸發 ON UPDATE CASCADE）
UPDATE blogs SET id = 2 WHERE id = 1;
-- 檢查子表
SELECT * FROM comments;  -- blog_id 已自動變為 2

-- 刪除父表記錄（觸發 ON DELETE CASCADE）
DELETE FROM blogs WHERE id = 2;
-- 檢查子表
SELECT * FROM comments;  -- 關聯留言已自動刪除

進階場景與最佳實踐

多級級聯

A → B → C 三層關聯，若 A 刪除級聯到 B，B 刪除再級聯到 C。需注意：

避免循環引用（A→B→A）
大量級聯刪除可能鎖表久、產生大量 Binlog，建議分批處理

外鍵與效能平衡

大量批次操作：暫時停用外鍵檢查 SET FOREIGN_KEY_CHECKS=0; 操作完 SET FOREIGN_KEY_CHECKS=1;
線上加外鍵：大表加外鍵會鎖表，建議用 pt-online-schema-change 或維護窗口
查詢優化：外鍵索引可被 JOIN 利用，EXPLAIN 確認 ref 類型

遷移 MyISAM 到 InnoDB 加外鍵

-- 1. 轉換引擎
ALTER TABLE blogs ENGINE=InnoDB;
ALTER TABLE comments ENGINE=InnoDB;

-- 2. 確保欄位型別完全一致（含 UNSIGNED、長度）
-- 3. 清理髒資料（孤兒記錄）
DELETE c FROM comments c LEFT JOIN blogs b ON c.blog_id = b.id WHERE b.id IS NULL;

-- 4. 加外鍵
ALTER TABLE comments 
    ADD CONSTRAINT fk_comments_blog 
    FOREIGN KEY (blog_id) REFERENCES blogs(id)
    ON UPDATE CASCADE ON DELETE CASCADE;

常見錯誤與排查

錯誤	原因	解決
Cannot add foreign key constraint	欄位型別不符、父表無主鍵/唯一索引、資料不一致	對齊型別、建索引、清理髒資料
Foreign key constraint fails	插入子表時父表無對應值、刪除父表時子表有關聯且非 CASCADE	先插父表、或改 CASCADE
Deadlock found	並發更新父子表順序不一致	應用層固定加鎖順序（先父後子）