生產環境常因設計階段未加 ON DELETE CASCADE,導致誤入髒資料後難以級聯清理。本文深度對比四種實現級聯刪除的方案:**建立時指定、事後重建約束、觸發器實現、手動逐層刪除**,分析適用場景、優缺點、風險控制與實戰腳本,助您根據業務複雜度、資料量、停機容忍度選擇最佳方案。
CREATE TABLE parent_tab (
far_id NUMBER(10) PRIMARY KEY,
far_name VARCHAR2(10)
);
CREATE TABLE child_tab (
chil_id NUMBER(10) PRIMARY KEY,
far_id NUMBER(10) NOT NULL,
CONSTRAINT fk_child_parent FOREIGN KEY (far_id)
REFERENCES parent_tab(far_id) ON DELETE CASCADE
);
-- 1. 刪除原外鍵
ALTER TABLE child_tab DROP CONSTRAINT fk_child_parent;
-- 2. 重建帶 CASCADE 的外鍵
ALTER TABLE child_tab ADD CONSTRAINT fk_child_parent
FOREIGN KEY (far_id) REFERENCES parent_tab(far_id) ON DELETE CASCADE;
-- 3. 可選:若既有資料可能違反約束,暫不驗證 (NOVALIDATE)
ALTER TABLE child_tab ADD CONSTRAINT fk_child_parent
FOREIGN KEY (far_id) REFERENCES parent_tab(far_id) ON DELETE CASCADE NOVALIDATE;
-- 後續另行驗證: ALTER TABLE child_tab VALIDATE CONSTRAINT fk_child_parent;
NOVALIDATE 避開既有髒資料阻礙。DROP CONSTRAINT 瞬間釋放鎖,極短時間窗口允許違反 FK 的資料寫入;建議維護窗口執行或鎖表。-- 1. 建表 (無 FK 或 FK 無 CASCADE)
CREATE TABLE order_combine (
o_id VARCHAR2(16) PRIMARY KEY,
order_id VARCHAR2(15) NOT NULL,
former_id VARCHAR2(16) NOT NULL
);
CREATE TABLE vip_form (
v_id VARCHAR2(16) NOT NULL,
is_valid CHAR(1) DEFAULT '0',
CONSTRAINT fk_vip_order FOREIGN KEY (v_id) REFERENCES order_combine(former_id)
);
-- 2. BEFORE DELETE 觸發器於父表
CREATE OR REPLACE TRIGGER trg_cascade_del_order
BEFORE DELETE ON order_combine
FOR EACH ROW
BEGIN
DELETE FROM vip_form WHERE v_id = :OLD.former_id;
END;
/
| 維度 | 評價 |
|---|---|
| 靈活度 | 極高:可實現複雜邏輯(條件刪除、跨 Schema、非 PK/UK 參考、寫入稽核表、呼叫 API) |
| 效能 | 逐列觸發,大量刪除有上下文切換開銷;可改用 FOR EACH STATEMENT + 臨時表批次處理 |
| 遞迴支援 | 需手動編寫遞迴邏輯或多層觸發器,易形成觸發器鏈難以維護 |
| 可見性 | 隱性邏輯,易被忽略;需完整文檔化 |
| 備份/恢復/匯入 | 觸發器隨 Schema 匯出,但 Data Pump 需注意 TRIGGERS=Y |
| 適用場景 | 參考鍵非 PK/UK、需複雜業務邏輯、跨資料庫連結、舊系統無法重建約束 |
-- 適用:僅 2 層、資料量小 (千筆內)、可接受人工執行順序
DELETE FROM child_tab WHERE far_id = :target_id;
DELETE FROM parent_tab WHERE far_id = :target_id;
| 決策因子 | 方案一: 建表指定 | 方案二: 重建約束 | 方案三: 觸發器 | 方案四: 手動刪除 |
|---|---|---|---|---|
| 資料量/複雜度 | 任意/高 | 任意/中高 | 中小/高複雜邏輯 | 極小/極簡單 |
| 參考鍵類型 | PK/UK | PK/UK | 任意 (含非唯一) | 任意 |
| 遞迴深度 | 自動無限 | 自動無限 | 手動編碼 | 人工控制 |
| 維護窗口需求 | 無 (設計期) | 短 (秒~分) | 無 | 無 |
| 效能 | 最佳 | 最佳 | 中 (逐列) | 人工控制 |
| 可見性/可維護性 | 高 (字典可見) | 高 (字典可見) | 低 (程式碼隱藏) | 高 (腳本可見) |
| 風險 | 低 | 中 (短暫無約束) | 中 (觸發器鏈、異常處理) | 高 (人為錯誤) |
FK_子表_父表。VALIDATE vs NOVALIDATE。NO ACTION 防誤刪。級聯刪除不是單一技術選擇,而是**架構決策**。聲明式約束(方案一/二)應為首選,觸發器(方案三)為特殊場景補充,手動刪除(方案四)僅限臨時小範圍。關鍵在於:在字典中看得到、效能可預期、邏輯可審計、恢復可控制。建議建立「外鍵管理規範文件」,記錄所有 FK 的級聯策略、驗證狀態、變更歷程,納入資料庫治理體系。