Oracle 存儲過程解決外鍵約束下主鍵修改的完整性衝突

問題背景

業務有時需要修改主鍵值，但當該主鍵被其他表的外鍵引用時，直接 UPDATE 會觸發完整性約束違規（ORA-02292）。Oracle 不支援 ON UPDATE CASCADE，需人工協調處理。

場景建模

-- 學生表（主鍵 S#）
CREATE TABLE Student (
  S# INTEGER PRIMARY KEY,
  sname VARCHAR2(20),
  age INTEGER
);

-- 課程表（主鍵 C#）
CREATE TABLE Course (
  C# INTEGER PRIMARY KEY,
  cname VARCHAR2(20),
  credit INTEGER
);

-- 成績表（雙外鍵）
CREATE TABLE SC (
  S# INTEGER,
  C# INTEGER,
  score INTEGER,
  CONSTRAINT fk_sc_stu FOREIGN KEY (S#) REFERENCES Student(S#) ON DELETE CASCADE,
  CONSTRAINT fk_sc_cou FOREIGN KEY (C#) REFERENCES Course(C#) ON DELETE CASCADE
);

需求：修改學生學號（S#），同步更新 SC 表關聯記錄。

解決方案對比

推薦方案：禁用約束法實現

前置準備：為外鍵命名

ALTER TABLE SC ADD CONSTRAINT fk_sc_stu FOREIGN KEY (S#) REFERENCES Student(S#);

存儲過程實現

CREATE OR REPLACE PROCEDURE change_student_id(
  p_old_id IN INTEGER,
  p_new_id IN INTEGER
) AS
BEGIN
  -- 1. 禁用外鍵約束（動態 SQL）
  EXECUTE IMMEDIATE 'ALTER TABLE SC DISABLE CONSTRAINT fk_sc_stu';
  
  -- 2. 更新主表主鍵
  UPDATE Student SET S# = p_new_id WHERE S# = p_old_id;
  
  -- 3. 更新子表外鍵
  UPDATE SC SET S# = p_new_id WHERE S# = p_old_id;
  
  -- 4. 重新啟用約束（會驗證現有數據）
  EXECUTE IMMEDIATE 'ALTER TABLE SC ENABLE CONSTRAINT fk_sc_stu';
  
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- 異常時嘗試恢復約束
    BEGIN
      EXECUTE IMMEDIATE 'ALTER TABLE SC ENABLE CONSTRAINT fk_sc_stu';
    EXCEPTION WHEN OTHERS THEN NULL;
    END;
    RAISE;
END;
/

關鍵技術點

• 動態 SQL：存儲過程中執行 DDL 必須用 EXECUTE IMMEDIATE
• 事務原子性：整個過程在單一事務中，任一步驟失敗全數回滾
• 異常處理：確保約束最終恢復啟用，避免數據庫處於無約束狀態
• 鎖定影響：DISABLE/ENABLE CONSTRAINT 會獲取獨佔 DDL 鎖，高併發環境需謹慎使用

進階考量

1. 多外鍵場景：若主鍵被多表引用，需在過程中循環禁用/啟用所有相關約束
2. 級聯更新替代：Oracle 12c+ 可考慮用 DBMS_REDEFINITION 線上重定義表結構
3. 審計追蹤：建議記錄主鍵變更日誌（舊值、新值、操作時間、操作人）