MySQL ERROR 1005 (errno: 150) 外鍵建立失敗：兩大核心原因與修復實例

錯誤現象

ERROR 1005 (HY000): Can't create table 'xxx.frm' (errno: 150)
-- 或
ERROR 1005: Can't create table

執行 SHOW ENGINE INNODB STATUS 可見 LATEST FOREIGN KEY ERROR 詳細描述。

核心原因一：引用欄位類型/屬性不匹配 (最常見)

外鍵欄位與被引用主鍵**必須完全一致**：類型、長度、符號 (signed/unsigned)、字符集、排序規則。

失敗範例

CREATE TABLE booktype (
  btid INT(5) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
  btname VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE books (
  bid INT(5) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
  btid INT,  -- 缺少 UNSIGNED ZEROFILL
  FOREIGN KEY (btid) REFERENCES booktype(btid)
);

修復：btid INT(5) UNSIGNED ZEROFILL 完全對齊。

核心原因二：被引用表非 InnoDB / 引用欄位無索引

• MySQL **僅 InnoDB 支援外鍵約束** (MyISAM 語法通過但不生效)。
• 被引用欄位 (父表主鍵/唯一鍵) **必須有索引** (主鍵/唯一鍵自帶)。
• 引用欄位 (子表外鍵列) **必須顯式建立索引** (InnoDB 5.1+ 自動建立，舊版/某情況需手動)。

失敗範例

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE line_items (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  product_id INT NOT NULL,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id)
  -- 缺少 INDEX(product_id) (舊版需手動)
) ENGINE=InnoDB;  -- 必須指定

修復：
1. 兩表均 ENGINE=InnoDB。
2. 子表加 INDEX (product_id) (或 KEY)。

完整修復檢查清單

1. 父表引擎 = InnoDB。
2. 父表引用列為 PRIMARY KEY 或 UNIQUE KEY。
3. 子表引擎 = InnoDB。
4. 子表外鍵列類型、長度、符號、字符集、排序規則 **逐項對齊** 父表引用列。
5. 子表外鍵列有索引 (INDEX/KEY)。
6. 外鍵名稱在庫內唯一 (CONSTRAINT fk_name)。
7. 若父表已有數據，子表外鍵值必須存在於父表 (或為 NULL)。

診斷神器

SHOW ENGINE INNODB STATUS\G
-- 輸出中搜尋
------------------------
LATEST FOREIGN KEY ERROR
------------------------
... 具體不匹配細節 ...

總結

ERROR 1005 (errno 150) 99% 為**類型不匹配**或**引擎/索引缺失**。逐項對齊定義、強制 InnoDB、補全索引即可解決。