您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 外鍵與參考完整性實戰：InnoDB 建立、驗證、刪除與效能平衡指南

編輯：MySQL 綜合教學

前置條件

MySQL 早期版本不支援外鍵，InnoDB 儲存引擎 引入後才完整支援。建立外鍵需滿足三條件：

父表與子表均為 InnoDB
外鍵欄位在子表有索引
父表參考欄位（通常為主鍵）有索引，且資料型別完全一致

建立外鍵完整範例

-- 父表：物種代碼表
CREATE TABLE `species` (
    `id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL
) ENGINE=InnoDB;

INSERT INTO `species` VALUES (1,'orangutan'),(2,'elephant'),(3,'hippopotamus'),(4,'yak');

-- 子表：動物園動物表
CREATE TABLE `zoo` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `fk_species` TINYINT UNSIGNED NOT NULL,
    INDEX `idx_fk_species` (`fk_species`),  -- 必須建立索引
    CONSTRAINT `fk_zoo_species` FOREIGN KEY (`fk_species`) REFERENCES `species` (`id`)
) ENGINE=InnoDB;

驗證外鍵約束生效

-- 合法插入
INSERT INTO `zoo` (`name`, `fk_species`) VALUES ('Harry', 3);  -- 成功，species.id=3 存在

-- 非法插入
INSERT INTO `zoo` (`name`, `fk_species`) VALUES ('Harry', 5);  -- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

查看與管理外鍵

-- 查看表建立語句（含外鍵名稱）
SHOW CREATE TABLE `zoo`;

-- 刪除外鍵（需用內部約束名稱）
ALTER TABLE `zoo` DROP FOREIGN KEY `fk_zoo_species`;

-- 重新添加
ALTER TABLE `zoo` ADD CONSTRAINT `fk_zoo_species` FOREIGN KEY (`fk_species`) REFERENCES `species` (`id`);

外鍵參考動作（ON DELETE / ON UPDATE）

-- 範例：級聯刪除
CONSTRAINT `fk_order_items` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE

效能與架構權衡

動作	行為	適用場景
`RESTRICT` / `NO ACTION`	拒絕父表刪除/更新（預設）	核心主資料，嚴禁級聯變更
`CASCADE`	自動刪除/更新子表關聯行	明確從屬關係（訂單-明細、文章-評論）
`SET NULL`	子表外鍵設為 NULL（欄位需允許 NULL）	可選關聯（用戶-頭像、文章-分類）
`SET DEFAULT`	子表外鍵設為預設值	極少用，需預設值存在於父表

優點	缺點/成本
資料完整性由資料庫層保證，應用層無需重複驗證	每次 INSERT/UPDATE/DELETE 需檢查父表，增加 CPU/鎖開銷
防止孤兒資料、臟資料產生	大量批次操作時鎖爭用明顯，可能成為瓶頸
級聯操作簡化應用邏輯	級聯刪除可能觸發連鎖反應，難以追蹤與控制