萬盛學電腦網

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

Oracle 外鍵級聯刪除與置空:ON DELETE CASCADE vs SET NULL 實戰詳解

問題背景

預設情況下,Oracle 禁止刪除被子表引用的父表記錄(報錯 `ORA-02292`)。9i 引入 `ON DELETE CASCADE` 與 `ON DELETE SET NULL` 兩種級聯動作,賦予開發者靈活的業務邏輯控制權。本文通過 `dept`/`emp` 經典模型演示差異與陷阱。

環境準備

-- 父表:部門
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),
    CONSTRAINT pk_emp PRIMARY KEY (empno)
);

模式一:ON DELETE CASCADE —— 連帶刪除

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

插入測試數據:

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);

執行刪除:

DELETE FROM dept WHERE deptno = 1;

結果:部門 1 被刪除,員工表中 `dept=1` 的 Mary、Linlin 兩條記錄**自動連帶刪除**。適用於「子數據無獨立業務意義、隨父數據生命週期結束」的場景(如 訂單-明細、發票-行項)。

模式二:ON DELETE SET 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;

再次刪除部門 1:

DELETE FROM dept WHERE deptno = 1;

結果:部門 1 刪除,員工表中對應行的 `dept` 欄位自動置為 `NULL`。適用於「子記錄獨立存在、僅關聯鬆散」的場景(如 員工-部門、文章-分類)。

關鍵限制:子表欄位不能有 NOT NULL

若 `emp.dept` 定義了 `NOT NULL`,再用 `ON DELETE SET NULL` 會報錯:

ORA-01407: cannot update ("SCOTT"."EMP"."DEPT") to NULL

設計鐵律:使用 `SET NULL` 時,外鍵欄位**必須允許 NULL**。

三種刪除策略決策矩陣

策略語法行為典型場景
預設限制無關鍵字禁止刪除父記錄核心主數據,嚴禁誤刪
級聯刪除ON DELETE CASCADE子記錄同步刪除明細表、日誌表、臨時關聯
級聯置空ON DELETE SET NULL子記錄外鍵置 NULL可選關聯、歷史保留需求

最佳實踐建議

  1. 默認拒絕:無特殊業務理由,保持預設 `RESTRICT` 行為,強制應用層顯式處理依賴。
  2. 文檔化決策:在數據字典或 ER 圖中標註每個 FK 的 `ON DELETE` 行為,避免隱性邏輯導致生產事故。
  3. 測試驗證:上線前在測試庫模擬批量刪除,確認級聯範圍與性能影響(大量 CASCADE 可能引發遞歸鎖競爭)。
copyright © 萬盛學電腦網 all rights reserved