MySQL ERROR 1005 (HY000) Can't create table 完整排查指南：外鍵約束兩大陷阱

錯誤現象

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

執行 SHOW ENGINE INNODB STATUS 查看 LATEST FOREIGN KEY ERROR 區塊可得詳細原因。

陷阱一：參考欄位類型不完全一致

錯誤範例

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)
);

解決

外鍵欄位必須與被參考欄位類型、長度、符號、ZEROFILL 屬性完全一致：

btid INT(5) UNSIGNED ZEROFILL  -- ✅ 修正後

陷阱二：被參考表缺乏索引或引擎不支援

錯誤範例

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

CREATE TABLE line_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES producets(id),  -- ❌ 拼寫錯誤：producets
    INDEX (product_id)
) ENGINE=InnoDB;

解決

1. 表引擎必須為 InnoDB（MyISAM 不支援外鍵）。
2. 被參考欄位必須有索引（主鍵自動有，非主鍵需額外建立）。
3. 拼寫必須精確：REFERENCES products(id)。
4. 外鍵欄位同樣需建立索引（INDEX (product_id)），否則 UPDATE/DELETE 父表時需全表掃描鎖定子表，效能極差。

完整檢核清單

排查指令

SHOW ENGINE INNODB STATUS\G
# 關注 LATEST FOREIGN KEY ERROR 區塊

SHOW CREATE TABLE parent_table\G
SHOW CREATE TABLE child_table\G
# 對比類型、索引、引擎