Oracle 外鍵級聯操作：ON DELETE CASCADE/SET NULL/NO ACTION 詳解

Oracle 外鍵級聯操作：ON DELETE CASCADE/SET NULL/NO ACTION 詳解

定義外鍵約束時，可透過 ON DELETE 子句指定父表刪除記錄時，子表外鍵欄位的處理方式。Oracle 支援三種模式，各有適用場景與限制。

三種級聯模式對比

實戰範例：SET NULL 模式

-- 建立父表：班級
CREATE TABLE stuclass (
  cid NUMBER(4) PRIMARY KEY,
  cname VARCHAR2(40),
  num NUMBER(4)
);

-- 建立子表：學生，外鍵指定 ON DELETE SET NULL
CREATE TABLE stu (
  sid NUMBER(4),
  scode VARCHAR(10),
  sname VARCHAR(20),
  cid NUMBER(4) REFERENCES stuclass(cid) ON DELETE SET NULL
);

-- 測試資料
INSERT INTO stuclass VALUES (1, 'Oracle班', 35);
INSERT INTO stu VALUES (1, 'S001', '小王', 1);

-- 刪除父表記錄
DELETE FROM stuclass WHERE cid = 1;

-- 結果驗證
SELECT * FROM stuclass;  -- 無資料
SELECT * FROM stu;
-- SID SCODE SNAME CID
-- 1   S001  小王   (null)  <-- 外鍵自動設為 NULL

實戰範例：CASCADE 模式

-- 修改外鍵為 CASCADE (需先刪除舊約束)
ALTER TABLE stu DROP CONSTRAINT SYS_C0010080;  -- 查詢 user_constraints 取得名稱
ALTER TABLE stu ADD CONSTRAINT fk_stu_class 
  FOREIGN KEY (cid) REFERENCES stuclass(cid) ON DELETE CASCADE;

-- 重新測試
INSERT INTO stuclass VALUES (1, 'Oracle班', 35);
INSERT INTO stu VALUES (1, 'S001', '小王', 1);
DELETE FROM stuclass WHERE cid = 1;
SELECT * FROM stu;  -- 無資料，學生記錄同步刪除

重要限制與注意事項

• SET NULL 需允許 NULL：外鍵欄位若有 NOT NULL 約束，建立 ON DELETE SET NULL 會報錯 ORA-02299
• CASCADE 遞迴觸發：若孫表也有 CASCADE，刪除祖表會層層遞刪，需謹慎設計
• 效能影響：大量刪除時 CASCADE 會產生大量遞迴 SQL，考慮分批或停用約束後手動處理
• DDL 限制：父表主鍵欄位若被外鍵引用，無法直接 DROP COLUMN 或 MODIFY 型別
• 預設行為：未指定 ON DELETE 等同 NO ACTION，Oracle 不支援 RESTRICT 關鍵字 (PostgreSQL/MySQL 有)

查詢現有外鍵級聯設定

SELECT 
  uc.constraint_name,
  uc.table_name AS child_table,
  ucc.column_name AS child_column,
  uc.r_constraint_name AS parent_constraint,
  ucp.table_name AS parent_table,
  uc.delete_rule  -- CASCADE / SET NULL / NO ACTION
FROM user_constraints uc
JOIN user_cons_columns ucc ON uc.constraint_name = ucc.constraint_name
JOIN user_constraints ucp ON uc.r_constraint_name = ucp.constraint_name
WHERE uc.constraint_type = 'R'
ORDER BY uc.table_name, uc.constraint_name;

最佳實踐建議

1. **明確業務語意**：刪除父實體時，子實體是「必須消失」(CASCADE)、「保留但斷開關聯」(SET NULL)、「禁止刪除」(NO ACTION)
2. **文檔化約束名稱**：使用有意義名稱如 FK_ORDER_CUSTOMER，避免系統生成的 SYS_C00xxxx
3. **測試邊界案例**：批量刪除、遞迴級聯、NULL 值處理
4. **監控約束違規**：應用層捕捉 ORA-02292 (子記錄存在) 轉為友善錯誤訊息