Oracle 9i 引入 ON DELETE CASCADE 與 ON DELETE SET NULL 子句,賦予外鍵約束聲明式引用完整性維護能力,避免了觸發器實現的複雜度與效能損耗。本文通過完整 DEMO 演示兩種模式的行為差異、約束條件、鎖機制與應用場景選型指南。
-- 部門表 (父表)
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);
COMMIT;
-- 刪除父表記錄
DELETE FROM dept WHERE deptno = 1;
-- 結果驗證
SELECT * FROM dept; -- 僅剩 deptno=2
SELECT * FROM emp; -- 僅剩 empno=3 (Linda, dept=2)
-- 結論:父表刪除 → 子表關聯行自動級聯刪除
CREATE INDEX idx_emp_dept ON emp(dept)),否則全表掃描鎖定全表。-- 先恢復數據並修改約束
ROLLBACK; -- 或重新插入
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;
-- 刪除父表記錄
DELETE FROM dept WHERE deptno = 1;
-- 結果驗證
SELECT * FROM dept; -- 僅剩 deptno=2
SELECT * FROM emp; -- empno=2,4 的 dept 變為 NULL,empno=3 保持 2
-- 結論:父表刪除 → 子表關聯外鍵列置 NULL
dept NUMBER(10) NOT NULL,執行刪除會拋出 ORA-01407: cannot update ("SCHEMA"."EMP"."DEPT") to NULL。| 策略 | 語法 | 父表刪除時行為 | 適用業務語義 | 風險點 |
|---|---|---|---|---|
| 限制 (預設) | 無額外子句 | 阻止刪除,拋 ORA-02292 | 核心主數據 (客戶、產品)、嚴格審計要求 | 應用需捕獲異常並提示用戶 |
| 級聯刪除 | ON DELETE CASCADE | 自動刪除所有子行 | 明細表 (訂單項、日誌條目)、臨時關聯數據 | 誤刪導致數據雪崩、大事務 UNDO 膨脹 |
| 置空關聯 | ON DELETE SET NULL | 子表外鍵列置 NULL | 可選關聯 (員工-部門、文章-分類)、軟刪除模式 | 外鍵列必須可空、語義模糊需應用層處理 |
Oracle 不支援 ON UPDATE CASCADE。若需主鍵值變更同步到子表,需採用:
BEFORE UPDATE OF pk_col 觸發器更新子表,需處理並發鎖順序 (父→子)。DEFERRABLE INITIALLY DEFERRED 約束延遲檢查。DEFAULT NULL 顯式聲明意圖。DBA_CONSTRAINTS 中 DELETE_RULE 列 (CASCADE/SET NULL/NO ACTION) 是否符合設計。