MySQL 外鍵約束完全指南：建立、刪除、級聯動作與效能考量

外鍵核心作用

參考完整性：確保子表外鍵值必須存在於父表主鍵/唯一鍵中，防止孤兒資料。

一、建立外鍵兩大方式

1. CREATE TABLE 時建立

CREATE TABLE city (
    city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    city VARCHAR(50) NOT NULL,
    country_id SMALLINT UNSIGNED NOT NULL,
    KEY idx_fk_country_id (country_id),  -- 建議先建索引
    CONSTRAINT fk_city_country 
        FOREIGN KEY (country_id) 
        REFERENCES country (country_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,
    PRIMARY KEY (city_id)
) ENGINE=InnoDB;

2. ALTER TABLE 後加外鍵

-- 步驟 1：建立索引 (外鍵欄位必須有索引)
ALTER TABLE city ADD INDEX idx_city_country (country_id);

-- 步驟 2：加外鍵約束 (注意用 ADD 而非 MODIFY)
ALTER TABLE city 
ADD CONSTRAINT fk_city_country 
FOREIGN KEY (country_id) 
REFERENCES country (country_id)
ON DELETE CASCADE;

二、刪除外鍵

-- 必須知道約束名稱
ALTER TABLE city DROP FOREIGN KEY fk_city_country;

-- 若不知名稱，先查看
SHOW CREATE TABLE city\G
-- 輸出中找到 CONSTRAINT `fk_xxx` FOREIGN KEY ...

三、外鍵動作詳解 (ON DELETE / ON UPDATE)

關鍵規則

• ON DELETE RESTRICT 和 ON DELETE CASCADE **不能同時指定**
• 預設行為：插入子表時父表必須存在；刪除父表時若子表有引用則拒絕
• 級聯更新：父表 PK 變更 → 子表 FK 自動同步 (需 ON UPDATE CASCADE)

四、效能與鎖影響 (生產環境必讀)

五、臨時關閉外鍵檢查

-- 關閉 (匯入資料、大批量操作、重建表)
SET FOREIGN_KEY_CHECKS = 0;

-- 執行操作...
LOAD DATA INFILE ...;
TRUNCATE TABLE ...;

-- 開啟 (務必恢復！)
SET FOREIGN_KEY_CHECKS = 1;

適用場景： 多表資料匯入順序不確定、LOAD DATA、ALTER TABLE 重建表、從備份恢復。

六、最佳實踐與避坑

1. 外鍵欄位必須建索引：MySQL 會自動建，但顯式建可控制索引名、類型
2. 明確命名 CONSTRAINT：fk_子表_父表，便於管理與刪除
3. 謹慎用 CASCADE：避免誤刪連鎖反應，核心業務表建議 RESTRICT + 應用層軟刪除
4. 大表避免外鍵：億級表外鍵開銷大，改用應用層校驗 + 定期一致性掃描
5. 字符集排序規則一致：父子表 FK/PK 欄位字符集、排序規則必須完全一致
6. 監控指標：Innodb_row_lock_waits、Innodb_deadlocks、外鍵檢查耗時

七、外鍵 vs 應用層完整性對比

總結

外鍵是 **資料完整性的守門員**，用得好是護身符，用不好是效能殺手。**中小型核心業務表建議啟用，大表高併發場景建議應用層接管**。無論選擇哪種，**索引、監控、死鎖預案** 都是必修課。