MySQL ERROR 1005 (HY000) Can't create table 完整排查：外鍵約束與索引不匹配雙重陷阱

錯誤現象

ERROR 1005 (HY000): Can't create table `db`.`tbl` (errno: 150)

執行 SHOW ENGINE INNODB STATUS 查看 LATEST FOREIGN KEY ERROR 區塊，核心提示：

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint.

兩大核心原因與修復

原因一：外鍵欄位型別不匹配（最常見）

錯誤範例

CREATE TABLE booktype (
    btid INT(5) UNSIGNED ZEROFILL AUTO_INCREMENT PRIMARY KEY,
    btname VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE books (
    bid INT AUTO_INCREMENT PRIMARY KEY,
    btid INT,  -- 缺少 UNSIGNED ZEROFILL
    FOREIGN KEY (btid) REFERENCES booktype(btid)
) ENGINE=InnoDB;  -- ERROR 1005

修復

ALTER TABLE books MODIFY btid INT(5) UNSIGNED ZEROFILL;
-- 或建表時完整匹配
btid INT(5) UNSIGNED ZEROFILL,

規則：引用欄位與被引用欄位的型別、長度、符號（UNSIGNED）、字元集、排序規則必須**完全一致**。

原因二：被引用表缺少索引（或索引欄位順序錯誤）

錯誤範例

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE line_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    FOREIGN KEY (product_id) REFERENCES products(id)  -- products.id 有 PK 索引，正常
) ENGINE=InnoDB;

-- 若被引用欄位非主鍵且無索引
CREATE TABLE categories (
    cat_id INT,
    name VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cat_id INT,
    FOREIGN KEY (cat_id) REFERENCES categories(cat_id)  -- ERROR 1005
) ENGINE=InnoDB;

修復

ALTER TABLE categories ADD INDEX idx_cat_id (cat_id);
-- 或建表時
cat_id INT, INDEX idx_cat_id (cat_id),

規則：被引用欄位必須擁有索引，且該欄位必須是索引的最左前綴（複合索引時）。

完整排查清單

1. 執行 SHOW ENGINE INNODB STATUS 獲取詳細錯誤。
2. 核對 REFERENCES tbl(col) 型別完全一致（SHOW CREATE TABLE 對比）。
3. 確認被引用表 col 有索引且為最左列。
4. 確認兩表皆為 ENGINE=InnoDB（MyISAM 不支援外鍵）。
5. 檢查字元集/排序規則一致（CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci）。
6. 若仍失敗，嘗試先建表再 ALTER TABLE ADD FOREIGN KEY 分步驟定位。