MySQL 外鍵級聯更新實戰：從失敗到成功的 ALTER TABLE 避坑指南

研讀《深入淺出 MySQL》時發現：書中稱外鍵可實現「父表更新同步到子表」，但實測卻報錯 ERROR 1451 (23000): Cannot delete or update a parent row。本文記錄從錯誤到修復的完整過程，揭示 ON UPDATE CASCADE 缺失與 ALTER TABLE 修改外鍵的正確姿勢。

場景還原：缺失 CASCADE 的外鍵

父表 country、子表 city，外鍵定義為：

CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) 
REFERENCES `country` (`country_id`)

嘗試更新父表主鍵：

UPDATE country SET country_id=100 WHERE country_id=1;
-- ERROR 1451: foreign key constraint fails

原因明確：外鍵定義未包含 ON UPDATE CASCADE，預設為 RESTRICT 阻止父鍵變更。

曲折嘗試：直接 ADD CONSTRAINT 失敗

嘗試直接新增帶 CASCADE 的外鍵：

ALTER TABLE city ADD CONSTRAINT `city_ibfk_1` 
FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE;
-- ERROR 1005 (HY000): Can't create table ... (errno: 121)
-- Duplicate key on write or update

錯誤 121 提示鍵名衝突，因原外鍵尚未刪除。

正確路徑：DROP 再 ADD，兩步到位

-- 步驟 1：刪除舊外鍵（需指定具體名稱）
ALTER TABLE city DROP FOREIGN KEY `city_ibfk_1`;

-- 步驟 2：新增帶 CASCADE 的外鍵
ALTER TABLE city ADD FOREIGN KEY (`country_id`) 
REFERENCES `country` (`country_id`) ON UPDATE CASCADE;

驗證結果：

SHOW CREATE TABLE city\G
-- CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) 
-- REFERENCES `country` (`country_id`) ON UPDATE CASCADE

再次測試更新父表，子表 country_id 自動同步更新，級聯生效。

關鍵經驗總結

• 外鍵預設行為是 RESTRICT，需顯式聲明 CASCADE、SET NULL、NO ACTION 等。
• 修改外鍵必須「先刪後加」，不可直接 ADD 同名約束，也不可用 MODIFY。
• 建表時一次性定義完整（含 CASCADE）最省事，避免後續 DDL 鎖表風險。
• 若遇 errno 150，請參考上一篇《10 大核心原因排查清單》逐項核對。

掌握外鍵 DDL 正確姿勢，讓資料完整性約束真正為業務護航，而非成為開發阻力。