MySQL 外鍵建立失敗 Error 1005 完整排查清單 (6 大常見原因)

錯誤現象

執行 ALTER TABLE ... ADD FOREIGN KEY 時拋出：ERROR 1005 (HY000): Can't create table ... (errno: 150) 或 ERROR 1215 (HY000): Cannot add foreign key constraint。核心原因通常落在以下 6 大類：

1. 儲存引擎非 InnoDB ❌

主表、從表皆必須為 InnoDB。MyISAM、MEMORY 等引擎不支援外鍵。

SHOW TABLE STATUS WHERE Name IN ('parent_tbl', 'child_tbl'); -- 檢查 Engine 欄位

2. 參照欄位不存在或名稱錯誤 🔍

父表中必須存在被參照的欄位 (通常是 PRIMARY KEY 或 UNIQUE KEY)，且拼寫、大小寫需完全一致。

3. 字符集與排序規則不一致 🌐

兩表、兩欄位的 CHARACTER SET 與 COLLATION 必須完全相同。常見坑：一表 utf8mb4，另一表 utf8；或一欄位 utf8_general_ci，另一欄位 utf8_unicode_ci。

SHOW CREATE TABLE parent_tbl;
SHOW CREATE TABLE child_tbl; -- 對比 CHARSET 與 COLLATE

4. 資料型別、長度、屬性不匹配 📐

外鍵欄位與參照欄位的 型別 (INT/BIGINT/VARCHAR...)、長度、UNSIGNED、ZEROFILL 等屬性必須一致。INT(11) vs BIGINT、VARCHAR(20) vs VARCHAR(30) 皆會失敗。

5. 參照欄位缺少索引 📇

父表的參照欄位必須有索引 (PRIMARY KEY、UNIQUE KEY 或普通 INDEX)。InnoDB 要求外鍵參照的欄位最左前綴有索引。

6. 現有資料違反參照完整性 🚫

子表已存在的資料中，外鍵欄位的值在父表找不到對應記錄。建立前先清理孤兒資料：

DELETE FROM child_tbl 
WHERE fk_col NOT IN (SELECT pk_col FROM parent_tbl);

快速診斷指令

-- 查看最近的外鍵錯誤詳細訊息
SHOW ENGINE INNODB STATUS\G
-- 關鍵段落: LATEST FOREIGN KEY ERROR

總結檢查清單

逐項排查，99% 的 Error 1005/1215 皆可解決。建立外鍵前先 SHOW CREATE TABLE 對比結構，可避開大多數坑。