萬盛學電腦網

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

Oracle 非預設外鍵約束:ON DELETE CASCADE 與 SET NULL 實戰詳解

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

模式一: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);
COMMIT;

-- 刪除父表記錄
DELETE FROM dept WHERE deptno = 1;

-- 結果驗證
SELECT * FROM dept;  -- 僅剩 deptno=2
SELECT * FROM emp;   -- 僅剩 empno=3 (Linda, dept=2)
-- 結論:父表刪除 → 子表關聯行自動級聯刪除

鎖與效能特徵

  • 刪除父表行時,Oracle 對子表相關行加排他鎖 (TX),大量子行時可能引發鎖爭用。
  • 需確保子表外鍵欄位有索引 (CREATE INDEX idx_emp_dept ON emp(dept)),否則全表掃描鎖定全表。
  • 遞歸級聯:若孫表也有 CASCADE,會遞歸刪除,需評估鏈長度對 UNDO/REDO 影響。

模式二:ON DELETE SET NULL (置空關聯)

-- 先恢復數據並修改約束
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

關鍵前置條件與陷阱

  • 子表外鍵列必須允許 NULL:若定義 dept NUMBER(10) NOT NULL,執行刪除會拋出 ORA-01407: cannot update ("SCHEMA"."EMP"."DEPT") to NULL。
  • 語義差異:CASCADE 表示「子實體不獨立存在」,SET NULL 表示「子實體獨立存在但關聯可選」。
  • 索引同樣必要:無索引同樣導致全表鎖定。

三種引用完整性策略對比

策略語法父表刪除時行為適用業務語義風險點
限制 (預設)無額外子句阻止刪除,拋 ORA-02292核心主數據 (客戶、產品)、嚴格審計要求應用需捕獲異常並提示用戶
級聯刪除ON DELETE CASCADE自動刪除所有子行明細表 (訂單項、日誌條目)、臨時關聯數據誤刪導致數據雪崩、大事務 UNDO 膨脹
置空關聯ON DELETE SET NULL子表外鍵列置 NULL可選關聯 (員工-部門、文章-分類)、軟刪除模式外鍵列必須可空、語義模糊需應用層處理

進階話題:ON UPDATE CASCADE (Oracle 不支援)

Oracle 不支援 ON UPDATE CASCADE。若需主鍵值變更同步到子表,需採用:

  1. 代理鍵 (Surrogate Key):主鍵使用序列/UUID,業務鍵加唯一索引,業務鍵變更不影響外鍵。
  2. 觸發器手動同步:父表 BEFORE UPDATE OF pk_col 觸發器更新子表,需處理並發鎖順序 (父→子)。
  3. 應用層事務:在單一事務中先更新子表再更新父表,配合 DEFERRABLE INITIALLY DEFERRED 約束延遲檢查。

最佳實踐清單

  • [ ] 所有外鍵欄位**必須建立索引** (除非表極小且永不並發)。
  • [ ] 明確業務語義選擇 CASCADE/SET NULL/RESTRICT,文檔化在數據字典。
  • [ ] CASCADE 鏈深度 > 3 級時,評估遞歸刪除的 UNDO/REDO/鎖影響,考慮應用層分批刪除。
  • [ ] SET NULL 列建議添加 DEFAULT NULL 顯式聲明意圖。
  • [ ] 定期檢查 DBA_CONSTRAINTS 中 DELETE_RULE 列 (CASCADE/SET NULL/NO ACTION) 是否符合設計。
copyright © 萬盛學電腦網 all rights reserved