萬盛學電腦網

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

MySQL 外鍵與參照完整性:InnoDB 實現約束的完整指南

核心概念

參照完整性:確保表間關聯字段始終指向存在的記錄,防止「孤兒數據」。

外鍵:在子表定義的約束,強制其列值必須存在於父表的主鍵/唯一鍵中。

MySQL 支持前提(三個硬性條件)

  1. 兩表均為 InnoDB 引擎
  2. 關聯列均建有索引(父表主鍵自帶,子表需顯式 INDEX(fk_col))
  3. 關聯列數據類型、長度、字符集、校對規則完全一致

完整建表示例

-- 父表:物種字典
CREATE TABLE species (
    id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

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

-- 子表:動物園動物
CREATE TABLE zoo (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    fk_species TINYINT UNSIGNED NOT NULL,
    INDEX (fk_species),                              -- 必須有索引
    FOREIGN KEY (fk_species) REFERENCES species(id), -- 定義外鍵
    PRIMARY KEY (id)
) ENGINE=InnoDB;

約束生效演示

-- 嘗試插入不存在的 species_id=5
INSERT INTO zoo VALUES (1, 'Harry', 5);
-- ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails

-- 正確插入
INSERT INTO zoo VALUES (1, 'Harry', 3);  -- species.id=3 存在
-- Query OK, 1 row affected

刪除外鍵約束

  1. SHOW CREATE TABLE zoo; 查看 InnoDB 內部約束名(如 zoo_ibfk_1)
  2. ALTER TABLE zoo DROP FOREIGN KEY zoo_ibfk_1;
  3. (可選)DROP INDEX fk_species ON zoo; 刪除不再需要的索引

進階選項:ON UPDATE / ON DELETE

FOREIGN KEY (fk_species) REFERENCES species(id)
    ON UPDATE CASCADE   -- 父表 id 變更,子表自動級聯更新
    ON DELETE RESTRICT  -- 父表有子記錄時禁止刪除(默認)

其他選項:SET NULL(需子表列允許 NULL)、NO ACTION(同 RESTRICT)、CASCADE(級聯刪除)。

性能與運維提示

  • 外鍵會增加寫入開銷(檢查父表),高併發寫入場景評估是否改用應用層校驗
  • 大批量導數據時可暫時 SET FOREIGN_KEY_CHECKS=0; 導入後再 =1 校驗
  • 避免長事務持有鎖導致死鎖,合理設計索引順序
copyright © 萬盛學電腦網 all rights reserved