萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 外鍵約束完整指南:語法、級聯動作、實戰操作與最佳實踐

前言

外鍵約束是關聯式資料庫維護參考完整性的核心機制。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

使用前提條件 (必須滿足)

  1. 父表與子表 均為 InnoDB,且非臨時表
  2. 外鍵欄位與參照欄位 資料型別相容:數值型長度/符號位一致;字串型長度可不同
  3. 外鍵欄位與參照欄位 均已建立索引 (子表外鍵列自動建立索引,父表參照列需為主鍵或唯一索引)
  4. 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 管理外鍵

-- 新增外鍵
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參考完整性表級可跨多列

表級 vs 列級約束

  • 列級:單欄位定義時或之後聲明 (如 col INT NOT NULL)
  • 表級:所有欄位定義後聲明,可跨多欄位 (如 PRIMARY KEY(a,b)、FOREIGN KEY(a,b) REFERENCES t(c,d))
  • DEFAULT、NOT NULL 無表級形式

多外鍵實戰範例

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;

最佳實踐建議

  1. 預設採用 ON UPDATE CASCADE ON DELETE RESTRICT:更新同步、刪除保護,平衡一致性與安全
  2. 避免過度級聯:深層 CASCADE 可能導致大量鎖競爭、長事務、死鎖風險
  3. 業務層也要校驗:資料庫約束是最後防線,應用層應預先檢查關聯存在性
  4. 大批量操作暫停外鍵檢查:SET FOREIGN_KEY_CHECKS=0; 導入資料後 SET FOREIGN_KEY_CHECKS=1; (謹慎使用,需確保資料乾淨)
  5. 命名約束:顯式指定 CONSTRAINT fk_users_province 便於維護與刪除
  6. 監覽資訊表:定期檢查 information_schema.INNODB_SYS_FOREIGN 確認外鍵狀態

總結

外鍵約束是資料完整性的守門員。MySQL 僅 InnoDB 支援,核心在於 ON UPDATE/DELETE 參照動作選擇。RESTRICT (預設) 最安全,CASCADE 最便利但風險最高。實戰中推薦「更新級聯、刪除受限」組合,配合應用層軟刪除邏輯,兼顧效能、一致性與可維護性。DDL 變更時務必同步檢查外鍵依賴關係,避免隱性故障。

copyright © 萬盛學電腦網 all rights reserved