您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 非默認外鍵約束實戰：ON DELETE CASCADE vs SET NULL 深度對比

編輯：Oracle 資料庫基礎

前言

Oracle 默認外鍵行為為「限制刪除」(RESTRICT)，即父表記錄被子表引用時禁止刪除。實際業務中常需級聯刪除或置空。本文通過完整構建測試場景，深度對比 ON DELETE CASCADE 與 ON DELETE SET NULL 的行為差異、適用場景及注意事項。

一、測試環境構建

1. 父表：部門表

CREATE TABLE dept (
    deptno   NUMBER(10) NOT NULL,
    deptname VARCHAR2(30) NOT NULL,
    CONSTRAINT pk_dept PRIMARY KEY (deptno)
);

2. 子表：員工表

CREATE TABLE emp (
    empno  NUMBER(10) NOT NULL,
    fname  VARCHAR2(20),
    lname  VARCHAR2(20),
    dept   NUMBER(10),  -- 故意不加 NOT NULL，為 SET NULL 做準備
    CONSTRAINT pk_emp PRIMARY KEY (empno)
);

3. 測試數據

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 (級聯刪除)

1. 創建外鍵

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

2. 測試：刪除部門 1 (銷售部)

DELETE FROM dept WHERE deptno = 1;

3. 結果驗證

表	操作前	操作後	說明
DEPT	2 行 (1, 2)	1 行 (2)	部門 1 被刪除
EMP	3 行	1 行 (Linda, 部門 2)	Mary、Linlin 關聯部門 1，同步刪除

4. 適用場景

強生命週期綁定：訂單-明細、考試-答題、標題-內容
父刪除 = 子無意義，必須同步清理
數據量可控，避免誤觸發大量級聯刪除

三、方案二：ON DELETE SET NULL (置空關聯)

1. 先恢復數據並修改約束

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

-- 刪除舊約束，創建新約束
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;

2. 測試：再次刪除部門 1

DELETE FROM dept WHERE deptno = 1;

3. 結果驗證

表	操作前	操作後	說明
DEPT	2 行 (1, 2)	1 行 (2)	部門 1 被刪除
EMP	3 行	3 行	Mary、Linlin 的 dept 列變為 NULL，行保留

4. 關鍵前置條件：子表外鍵列必須允許 NULL

-- 若 emp.dept 定義了 NOT NULL
CREATE TABLE emp (
    ...
    dept NUMBER(10) NOT NULL,  -- 這裡加了 NOT NULL
    ...
);

-- 創建 ON DELETE SET NULL 會報錯
-- ORA-02299: cannot validate (FK) - null values found
-- 或刪除父記錄時：ORA-01407: cannot update ("SCOTT"."EMP"."DEPT") to NULL

5. 適用場景

弱關聯：員工-部門、學生-班級 (調班保留學生記錄)
審計要求：保留歷史記錄，僅切斷關聯
子表記錄獨立價值高，不可隨父刪除而消失

四、三種外鍵刪除策略決策矩陣

策略	語法	父刪除時子表行為	典型場景	風險
限制 (默認)	無需指定	報錯阻止刪除	嚴格主從、需人工確認	業務阻塞
級聯刪除	ON DELETE CASCADE	子行同步刪除	強綁定、明細無獨立價值	誤刪連鎖、性能衝擊
置空	ON DELETE SET NULL	外鍵列置 NULL，行保留	弱關聯、需保留子記錄	NULL 語義歧義、查詢需處理