MySQL 外鍵約束完整教學：建立、驗證與刪除實戰

前言

外鍵（Foreign Key）是維護「參照完整性」的關鍵機制，確保子表資料必須指向父表現有記錄。MySQL 3.23.34+ 僅在 InnoDB 引擎支援。本文涵蓋前置條件、語法、建表實戰、錯誤排查與刪除外鍵。

一、使用外鍵的四大前置條件

1. 啟用 InnoDB：my.cnf 取消註解 innodb_data_home_dir 等參數。
2. 表引擎為 InnoDB：ENGINE=InnoDB。
3. 關聯欄位有索引：MySQL 4.1+ 建外鍵自動建索引；舊版需手動 INDEX (col)。
4. 資料型別相容：父表 TINYINT → 子表可用 SMALLINT/INT/BIGINT；最好完全一致。

二、外鍵語法速查

-- 建表時定義
FOREIGN KEY [索引名] (欄位) REFERENCES 父表 (父欄位)
  [ON DELETE {RESTRICT|CASCADE|SET NULL|NO ACTION}]
  [ON UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION}]

-- ALTER TABLE 追加
ALTER TABLE 子表 ADD [CONSTRAINT 符號] FOREIGN KEY (欄位) REFERENCES 父表 (父欄位) ...;

-- 刪除外鍵
ALTER TABLE 子表 DROP FOREIGN KEY 符號;

參照動作說明：

• RESTRICT（預設）：父表有子記錄時禁止刪除/更新。
• CASCADE：級聯刪除/更新子表對應記錄。
• SET NULL：子表外鍵欄位設為 NULL（欄位需允許 NULL）。
• NO ACTION：同 RESTRICT。

三、完整建表實戰

-- 1. 父表：文章分類
CREATE TABLE categories (
    category_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(30) NOT NULL,
    PRIMARY KEY (category_id)
) ENGINE=InnoDB;

INSERT INTO categories (name) VALUES
('SQL Server'),('Oracle'),('PostgreSQL'),('MySQL'),('SQLite');

-- 2. 父表：會員
CREATE TABLE members (
    member_id INT UNSIGNED NOT NULL,
    name VARCHAR(20) NOT NULL,
    PRIMARY KEY (member_id)
) ENGINE=InnoDB;

INSERT INTO members VALUES (1,'test'),(2,'admin');

-- 3. 子表：文章（雙外鍵）
CREATE TABLE articles (
    article_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    category_id TINYINT UNSIGNED NOT NULL,
    member_id INT UNSIGNED NOT NULL,
    INDEX (category_id),
    FOREIGN KEY (category_id) REFERENCES categories (category_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_member FOREIGN KEY (member_id) REFERENCES members (member_id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    PRIMARY KEY (article_id)
) ENGINE=InnoDB;

四、驗證外鍵行為

-- 成功：category_id=3 存在、member_id=2 存在
INSERT INTO articles (category_id, member_id, title) VALUES (3, 2, 'bar');
-- Query OK

-- 失敗：category_id=6 不存在
INSERT INTO articles (category_id, member_id, title) VALUES (6, 1, 'foo');
-- ERROR 1452 (23000): Cannot add or update a child row: foreign key constraint fails

-- 失敗：member_id=3 不存在
INSERT INTO articles (category_id, member_id, title) VALUES (3, 3, 'foo');
-- ERROR 1452 (23000): foreign key constraint fails (`fk_member`)

五、刪除外鍵定義

1. 若建立時指定 CONSTRAINT fk_member：
   ALTER TABLE articles DROP FOREIGN KEY fk_member;
2. 若未指定，MySQL 自動產生符號（如 articles_ibfk_1），先查詢：
   SHOW CREATE TABLE articles;
   再刪除：
   ALTER TABLE articles DROP FOREIGN KEY articles_ibfk_1;

六、常見錯誤 ERROR 1005 (errno: 150) 排查清單

• 表引擎非 InnoDB。
• 父/子欄位型別、長度、簽署（UNSIGNED）不一致。
• 父表缺少索引（舊版需手動建立）。
• 父表主鍵/唯一鍵未定義。
• 字元集/校對規則不同（SHOW CREATE TABLE 確認）。

總結

外鍵以微小效能代價換取資料一致性保障。設計時務必確認四大前置條件，善用 CONSTRAINT 命名便於後續維護。大規模高併發場景可考慮應用層檢查替代，但中小型專案強烈建議啟用。