MySQL 常見錯誤代碼 1005/150 深度解析與外鍵約束最佳實踐

MySQL 常見錯誤代碼 1005/150 深度解析與外鍵約束最佳實踐

建表報錯 ERROR 1005 (HY000): Can't create table ... (errno: 121/150) 是外鍵約束配置不當的典型表現。本文系統梳理錯誌代碼含義、根因及規範化建表姿勢。

錯誤代碼速查

案例一：Errno 121 - 外鍵名衝突

錯誤場景： 兩張中間表 `role_privilege`、`user_role` 都引用 `role(id)`，且外鍵名均為 `role_id_FK`。

-- 錯誤
CREATE TABLE role_privilege (
  role_id VARCHAR(40),
  CONSTRAINT role_id_FK FOREIGN KEY (role_id) REFERENCES role(id)
);
CREATE TABLE user_role (
  role_id VARCHAR(40),
  CONSTRAINT role_id_FK FOREIGN KEY (role_id) REFERENCES role(id) -- 名稱衝突！
);

修正： 外鍵名需**全庫唯一**，建議採用 fk_表名_引用列_被引用表 命名規範：

CONSTRAINT fk_role_privilege_role_id_role FOREIGN KEY (role_id) REFERENCES role(id)
CONSTRAINT fk_user_role_role_id_role FOREIGN KEY (role_id) REFERENCES role(id)

案例二：Errno 150 - 外鍵定義不合法的四大元兇

1. 數據類型不匹配： 父表主鍵 INT，子表外鍵 CHAR/VARCHAR，或長度/符號位不一致（如 INT(11) vs BIGINT）。原則：精確一致，含 UNSIGNED。
2. 引用列無索引： InnoDB 要求**被引用列（父表主鍵/唯一鍵）必須有索引**，且**引用列（子表外鍵列）也會自動創建索引**。若父表無主鍵/唯一索引，報 150。
3. 字符集/校對規則不一致： 父表 utf8mb4，子表 latin1，或校對規則不同（_general_ci vs _bin）。原則：庫、表、列三級字符集完全一致。
4. 存儲引擎不支持： 父表或子表使用 MyISAM/MEMORY 等非事務引擎。原則：雙方必須為 InnoDB。

外鍵約束完整建表範本

-- 父表
CREATE TABLE `role` (
  `id` VARCHAR(40) NOT NULL COMMENT 'UUID',
  `name` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 子表
CREATE TABLE `user_role` (
  `user_id` VARCHAR(40) NOT NULL,
  `role_id` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`user_id`, `role_id`),
  -- 子表外鍵列自動創建索引，無需額外 INDEX
  CONSTRAINT `fk_user_role_role_id_role` FOREIGN KEY (`role_id`) 
    REFERENCES `role` (`id`) 
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_user_role_user_id_user` FOREIGN KEY (`user_id`) 
    REFERENCES `user` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

外鍵操作選項詳解

生產環境關於外鍵的爭議與建議

• 支持方： 數據庫層面保證引用完整性，防止髒數據，業務邏輯簡化。
• 反對方： 高併發下鎖競爭嚴重（插入子表需鎖父表行）、分庫分表/遷移困難、死鎖風險、性能開銷。
• 折中建議：
  • 核心強一致性場景（訂單-明細、用戶-賬戶）使用外鍵 + ON DELETE RESTRICT。
  • 高併發、大數據量、需水平擴展的業務（日志、流水、評論）**應用層維護完整性**，數據庫層不建外鍵，僅建索引。
  • 無論是否建外鍵，**子表關聯列必須建索引**，否則 UPDATE/DELETE 父表會全表掃描子表鎖表。

理解 Errno 121/150 背後的約束規則，配合規範化命名與類型定義，能消除 90% 的建表報錯，為數據完整性打下堅實基礎。