外鍵約束是關聯式資料庫維護參考完整性的核心機制。MySQL 僅 InnoDB 引擎 支援外鍵。本文系統整理外鍵語法、五種參照動作、建立/修改/刪除操作、多外鍵場景與實戰避坑指南。
[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
CONSTRAINT symbol 在資料庫中唯一 (未指定時 InnoDB 自動生成)| 動作 | 父表 DELETE/UPDATE 時子表行為 | 適用場景 |
|---|---|---|
| CASCADE | 自動級聯刪除/更新匹配的子表行 | 強一致性:訂單-明細、分類-商品 |
| SET NULL | 子表外鍵列設為 NULL (需列允許 NULL) | 可選關聯:用戶-訂單 (用戶刪除保留訂單) |
| NO ACTION | 標準 SQL 關鍵字,MySQL 中等同 RESTRICT | 標準相容性 |
| RESTRICT (預設) | 拒絕父表操作,拋出錯誤 | 保護核心主資料:字典表、配置表 |
| SET DEFAULT | 子表外鍵列設為預設值 | MySQL 不支援 |
-- 1. 父表更新級聯,刪除受限 (最常用)
ON UPDATE CASCADE ON DELETE RESTRICT
-- 2. 父表更新級聯,刪除級聯 (生命週期綁定)
ON UPDATE CASCADE ON DELETE CASCADE
-- 3. 父表更新級聯,刪除置空 (軟關聯)
ON UPDATE CASCADE ON DELETE SET NULL
CREATE TABLE provinces(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE users(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(10) NOT NULL,
pid SMALLINT UNSIGNED,
FOREIGN KEY(pid) REFERENCES provinces(id)
ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;
INSERT INTO provinces(pname) VALUES('A'),('B'),('C');
INSERT INTO users(username, pid) VALUES('Tom', 3); -- 關聯 provinces.id=3
-- 刪除 provinces.id=3 (RESTRICT: 失敗,因 users 有關聯)
DELETE FROM provinces WHERE id = 3;
-- Error: Cannot delete or update a parent row: a foreign key constraint fails
-- 修改為 CASCADE 後
ALTER TABLE users DROP FOREIGN KEY users_ibfk_1;
ALTER TABLE users ADD FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE;
-- 再次刪除 (成功,users 中 pid=3 的記錄同步刪除)
DELETE FROM provinces WHERE id = 3;
-- 新增外鍵
ALTER TABLE tbl_name
ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...)
REFERENCES tbl_name (col_name, ...)
[ON DELETE ...] [ON UPDATE ...];
-- 刪除外鍵 (需知約束名稱)
SHOW CREATE TABLE user2; -- 查看約束名,如 user2_ibfk_1
ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;
-- 僅刪除索引 (保留外鍵約束會自動重建索引)
ALTER TABLE user2 DROP INDEX pid;
| 約束類型 | 功能 | 層級 | 備註 |
|---|---|---|---|
| NOT NULL | 非空 | 列級 | 僅列級 |
| PRIMARY KEY | 主鍵唯一標識 | 表級/列級 | 每表僅一個 |
| UNIQUE KEY | 唯一值 | 表級/列級 | 允許多個 NULL |
| DEFAULT | 預設值 | 列級 | 僅列級 |
| FOREIGN KEY | 參考完整性 | 表級 | 可跨多列 |
col INT NOT NULL)PRIMARY KEY(a,b)、FOREIGN KEY(a,b) REFERENCES t(c,d))CREATE TABLE product(
category INT NOT NULL, id INT NOT NULL, price DECIMAL,
PRIMARY KEY(category, id)
) ENGINE=InnoDB;
CREATE TABLE customer(id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE product_order(
no INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_category INT NOT NULL, product_id INT NOT NULL,
customer_id INT NOT NULL,
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id) REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id) REFERENCES customer(id)
) ENGINE=InnoDB;
-- 查看表建立語句 (含外鍵定義)
SHOW CREATE TABLE tbl_name;
-- 查看索引詳情
SHOW INDEXES FROM tbl_name;
-- 診斷外鍵錯誤 (資訊 schema)
SELECT * FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY';
SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME IS NOT NULL;
ON UPDATE CASCADE ON DELETE RESTRICT:更新同步、刪除保護,平衡一致性與安全SET FOREIGN_KEY_CHECKS=0; 導入資料後 SET FOREIGN_KEY_CHECKS=1; (謹慎使用,需確保資料乾淨)CONSTRAINT fk_users_province 便於維護與刪除information_schema.INNODB_SYS_FOREIGN 確認外鍵狀態外鍵約束是資料完整性的守門員。MySQL 僅 InnoDB 支援,核心在於 ON UPDATE/DELETE 參照動作選擇。RESTRICT (預設) 最安全,CASCADE 最便利但風險最高。實戰中推薦「更新級聯、刪除受限」組合,配合應用層軟刪除邏輯,兼顧效能、一致性與可維護性。DDL 變更時務必同步檢查外鍵依賴關係,避免隱性故障。