Oracle 外鍵進階用法：ON DELETE CASCADE/SET NULL 級聯刪除實戰

前言

Oracle 9i 引入 ON DELETE CASCADE 與 ON DELETE SET NULL 兩種 Referential Action，讓外鍵約束具備自動處理子表資料的能力，避免應用層手動維護級聯邏輯。本文以部門/員工經典模型實戰演示兩種模式差異、建立語法、約束條件與最佳實踐。

一、基礎環境建立

-- 父表：部門
CREATE TABLE dept (
  deptno NUMBER(10) NOT NULL,
  deptname VARCHAR2(30) NOT NULL,
  CONSTRAINT pk_dept PRIMARY KEY (deptno)
);

-- 子表：員工
CREATE TABLE emp (
  empno NUMBER(10) NOT NULL,
  fname VARCHAR2(20),
  lname VARCHAR2(20),
  dept NUMBER(10),  -- 外鍵欄位，允許 NULL
  CONSTRAINT pk_emp PRIMARY KEY (empno)
);

-- 測試資料
INSERT INTO dept VALUES (1, '銷售部');
INSERT INTO dept VALUES (2, '財務部');
INSERT INTO emp VALUES (2, 'Mary', 'Song', 1);
INSERT INTO emp VALUES (3, 'Linda', 'Liu', 2);
INSERT INTO emp VALUES (4, 'Linlin', 'Zhang', 1);
COMMIT;

二、模式一：ON DELETE CASCADE（級聯刪除）

建立外鍵

ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept_cascade
FOREIGN KEY (dept) REFERENCES dept(deptno)
ON DELETE CASCADE;

執行刪除父表記錄

DELETE FROM dept WHERE deptno = 1;

結果

• 部門表：deptno=1 記錄刪除。
• 員工表：empno=2 (Mary)、empno=4 (Linlin) 同步刪除。
• 員工表：empno=3 (Linda, dept=2) 保留。

⚠ 級聯刪除會遞歸觸發：若 emp 又被其他表參考且也有 CASCADE，將連鎖刪除。需謹慎評估業務影響面。

三、模式二：ON DELETE SET NULL（置空外鍵）

前置條件：子表外鍵欄位必須允許 NULL

-- 若原欄位有 NOT NULL，必須先移除
ALTER TABLE emp MODIFY (dept NULL);

建立外鍵（先刪除舊約束）

ALTER TABLE emp DROP CONSTRAINT fk_emp_dept_cascade;
ALTER TABLE emp
ADD CONSTRAINT fk_emp_dept_setnull
FOREIGN KEY (dept) REFERENCES dept(deptno)
ON DELETE SET NULL;

恢復測試資料並執行刪除

INSERT INTO dept VALUES (1, '銷售部');
INSERT INTO emp VALUES (2, 'Mary', 'Song', 1);
INSERT INTO emp VALUES (4, 'Linlin', 'Zhang', 1);
COMMIT;

DELETE FROM dept WHERE deptno = 1;

結果

• 部門表：deptno=1 記錄刪除。
• 員工表：empno=2、4 的 dept 欄位自動設為 NULL，員工記錄保留。

四、三種 Referential Action 對比

五、常見錯誤與避坑

1. ORA-01407: cannot update to NULL：使用 SET NULL 但子表欄位有 NOT NULL 約束。→ 移除 NOT NULL 或改用 CASCADE。
2. 循環參考：Table A → Table B → Table A 皆有 CASCADE。→ 禁止循環 CASCADE，改用應用層邏輯或觸發器控制。
3. 效能衝擊：大量父表刪除觸發海量子表遞歸刪除/更新，產生大量 Undo/Redo。→ 批次處理時考慮暫停約束、手動分批刪除、最後重新啟用約束。
4. 查詢現有約束：

   SELECT constraint_name, delete_rule
   FROM user_constraints
   WHERE table_name = 'EMP' AND constraint_type = 'R';

六、最佳實踐建議

1. 業務語義優先：依「實體關係強弱」決定：強聚合 → CASCADE；弱關聯/可解除 → SET NULL；嚴格禁止孤兒 → RESTRICT。
2. 文檔化約束語義：在資料字典註解或設計文件明確記錄每個 FK 的 DELETE_RULE 選擇理由。
3. 測試驗證：部署前在測試環境模擬大量父表刪除，觀察子表變更量、執行時間、Undo 產生量。
4. 避免混用：同一父表對不同子表盡量統一策略，減少認知負擔。

七、總結

ON DELETE CASCADE 與 ON DELETE SET NULL 將級聯邏輯下推至資料庫核心，保證資料一致性且簡化應用代碼。選擇原則：「刪父必須刪子」→ CASCADE；「刪父子保留但解除關係」→ SET NULL（欄位可空）；「刪父禁止」→ RESTRICT。掌握三種模式差異與約束條件，即可靈活應對各類主從表設計場景。