預設情況下,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)
);
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 兩條記錄**自動連帶刪除**。適用於「子數據無獨立業務意義、隨父數據生命週期結束」的場景(如 訂單-明細、發票-行項)。
先恢復數據,修改約束:
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`。適用於「子記錄獨立存在、僅關聯鬆散」的場景(如 員工-部門、文章-分類)。
若 `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 | 可選關聯、歷史保留需求 |