MySQL 外鍵約束測試腳本：InnoDB CASCADE DELETE 驗證

測試環境建立

DROP TABLE IF EXISTS child;
DROP TABLE IF EXISTS parent;

CREATE TABLE parent (
  par_id INT NOT NULL,
  PRIMARY KEY (par_id)
) ENGINE=InnoDB;

CREATE TABLE child (
  par_id   INT NOT NULL,
  child_id INT NOT NULL,
  PRIMARY KEY (par_id, child_id),
  FOREIGN KEY (par_id) REFERENCES parent(par_id) ON DELETE CASCADE
) ENGINE=InnoDB;

測試資料

INSERT INTO parent (par_id) VALUES (1),(2),(3);
INSERT INTO child (par_id, child_id) VALUES (1,1),(1,2);
INSERT INTO child (par_id, child_id) VALUES (2,1),(2,2),(2,3);
INSERT INTO child (par_id, child_id) VALUES (3,1);

驗證 CASCADE DELETE

-- 刪除父表 par_id=1
DELETE FROM parent WHERE par_id = 1;

-- 查詢子表
SELECT * FROM child;
-- 結果：par_id=1 的兩筆記錄自動刪除，剩餘 par_id=2,3 的記錄

關鍵點總結

• 必須使用 InnoDB (MyISAM 不支援外鍵)
• ON DELETE CASCADE：刪除父列自動刪除關聯子列
• ON UPDATE CASCADE：更新父鍵自動更新子鍵
• 外鍵欄位需有索引 (子表自動建立、父表需為 PK/UK)
• 循環引用會報錯，需設計避免