Oracle 非默認外鍵約束：ON DELETE CASCADE 與 SET NULL 級聯行為深度解析

Oracle 默認外鍵約束為 NO ACTION（禁止刪除被引用行）。本文通過部門-員工模型，實測對比 ON DELETE CASCADE（級聯刪除）與 ON DELETE SET NULL（級聯置空）兩種非默認模式，明確適用場景與限制條件。

測試模型

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

結果

• dept 表：部門 1 被刪除
• emp 表：員工 2、4（屬部門 1）同步被刪除

適用：主從強綁定、從數據無獨立意義（如訂單-明細、考試-答題）

模式二：ON DELETE SET NULL

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

執行同樣刪除

DELETE FROM dept WHERE deptno = 1;

結果

• dept 表：部門 1 被刪除
• emp 表：員工 2、4 的 dept 列自動置為 NULL

關鍵限制：外鍵列不能有 NOT NULL 約束，否則報 ORA-01407: cannot update to NULL

適用：從數據獨立存在、業務允許「歸屬未知」狀態（如員工-部門、學生-班級）

決策矩陣

設計原則：優先考慮業務語義，輔以「最小驚訝原則」；CASCADE 需謹慎防止誤刪雪崩，SET NULL 需確保應用層能正確處理 NULL 外鍵。