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

Oracle 非預設外鍵約束實戰：ON DELETE CASCADE 與 ON DELETE SET NULL 完整指南

編輯：Oracle 資料庫基礎

預設行為：RESTRICT (NO ACTION)

建立外鍵未指定刪除規則時，Oracle 預設 ON DELETE NO ACTION (即 RESTRICT)：若父表資料被子表引用，**直接拋出 ORA-02292 禁止刪除**。

兩種級聯選項

子句	行為	適用場景	前置條件
`ON DELETE CASCADE`	刪除父表行時，自動刪除所有引用的子表行	主從強綁定，子表無獨立意義 (如訂單-明細、部門-員工)	無
`ON DELETE SET NULL`	刪除父表行時，將子表對應外鍵欄位設為 NULL	子表業務獨立，父表刪除不應連帶刪除子表 (如員工-部門，員工離職部門不消失)	子表外鍵欄位不可有 NOT NULL 約束

完整範例：部門 (DEPT) - 員工 (EMP)

1. 建立父子表


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

CREATE TABLE emp (
  empno  NUMBER(10) PRIMARY KEY,
  ename  VARCHAR2(20),
  deptno NUMBER(10)  -- 故意不加 NOT NULL，留給 SET NULL 用
);

2. 測試 ON DELETE CASCADE


-- 加上 CASCADE 外鍵
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_cascade 
  FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE CASCADE;

-- 測試資料
INSERT INTO dept VALUES (1, '銷售部');
INSERT INTO dept VALUES (2, '財務部');
INSERT INTO emp VALUES (101, 'Mary', 1);
INSERT INTO emp VALUES (102, 'John', 1);
INSERT INTO emp VALUES (103, 'Lisa', 2);

-- 刪除銷售部
DELETE FROM dept WHERE deptno = 1;
-- 結果：dept 刪 1 筆，emp 同步刪除 2 筆 (Mary, John)
SELECT * FROM emp; -- 僅剩 Lisa (deptno=2)

3. 測試 ON DELETE SET NULL


-- 先恢復資料
ROLLBACK; -- 或重新 INSERT

-- 改用 SET NULL (需先 DROP 舊 FK)
ALTER TABLE emp DROP CONSTRAINT fk_emp_dept_cascade;
ALTER TABLE emp ADD CONSTRAINT fk_emp_dept_setnull 
  FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL;

-- 同樣測試資料
INSERT INTO dept VALUES (1, '銷售部');
INSERT INTO dept VALUES (2, '財務部');
INSERT INTO emp VALUES (101, 'Mary', 1);
INSERT INTO emp VALUES (102, 'John', 1);
INSERT INTO emp VALUES (103, 'Lisa', 2);

-- 刪除銷售部
DELETE FROM dept WHERE deptno = 1;
-- 結果：dept 刪 1 筆，emp 中 deptno=1 的兩筆變為 NULL
SELECT * FROM emp;
-- 101 Mary NULL
-- 102 John NULL
-- 103 Lisa 2

4. SET NULL 失敗案例


-- 若 emp.deptno 有 NOT NULL
ALTER TABLE emp MODIFY deptno NUMBER(10) NOT NULL;
-- 再執行 DELETE FROM dept WHERE deptno = 1;
-- ORA-01407: cannot update ("SCOTT"."EMP"."DEPTNO") to NULL

三種刪除策略決策樹

需要刪除父表資料
    │
    ├─ 子表資料無獨立價值，必須隨父表消失 → ON DELETE CASCADE
    │
    ├─ 子表資料獨立保留，父表刪除不影響 → ON DELETE SET NULL (需外鍵欄位可空)
    │
    └─ 絕不允許刪除被引用父表 → 預設 RESTRICT (無需額外子句)

效能與鎖機制補充

CASCADE：父表刪除時對子表加 Row Exclusive (RX) 鎖，大量子表行刪除產生 Redo/Undo，可能觸發 遞迴刪除 (孫表再孫表)，需評估連鎖深度。
SET NULL：同樣加 RX 鎖，但只更新外鍵欄位，Redo 量較小。
兩者皆為**同步同交易**，父表刪除未提交前，子表變更不可見；父表回滾則子表變更一併回滾。
大批量刪除父表時，建議分批次 DELETE ... WHERE ROWNUM <= 10000 循環提交，避免長交易鎖死、Undo 表空間暴漲。

查詢現有外鍵刪除規則


SELECT constraint_name, table_name, delete_rule
FROM user_constraints
WHERE constraint_type = 'R'
ORDER BY table_name, constraint_name;

-- DELETE_RULE 值：CASCADE / SET NULL / NO ACTION