Oracle 非預設外鍵建法：ON DELETE CASCADE / SET NULL 實戰

預設行為痛點

預設外鍵建立時，父表資料被參考時 無法刪除（ORA-02292），維護不便。

9i 新增兩種刪除規則

完整範例

-- 父表
CREATE TABLE dept (
  deptno NUMBER(10) PRIMARY KEY,
  deptname VARCHAR2(30) NOT NULL
);

-- 子表
CREATE TABLE emp (
  empno NUMBER(10) PRIMARY KEY,
  fname VARCHAR2(20),
  lname VARCHAR2(20),
  dept NUMBER(10)
);

-- 加外鍵：級聯刪除
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 筆（Mary、Linlin）

改用 SET NULL 版本

ALTER TABLE emp DROP CONSTRAINT fk_emp_dept;
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 中 dept=1 的兩筆 dept 欄位變 NULL，員工保留

注意事項

• ON DELETE SET NULL 要求子表外鍵欄位未設 NOT NULL。
• 大量級聯刪除會產生大量 REDO/UNDO，影響效能與備份窗口，建議分批或配合分區表處理。
• 12c+ 支援 ON DELETE CASCADE 遞迴深度擴展，舊版本需注意遞迴上限。