Oracle 外鍵重命名規範與自動化腳本：從 SYS_Cxxx 到 FK_Child_Parent

為什麼外鍵命名規範至關重要？

外鍵維護參考完整性，涉及父表（被參考）與子表（參考者）。系統自動產生的名稱（如 SYS_C000231）在錯誤訊息中**完全無法識別涉及哪兩張表**，導致排查耗時。規範命名慣例：

FK_<子表簡名>_<父表簡名>
-- 例：FK_DJ_NSRXX_DM_GY_SWRY

長度限制 30 字元，必要時對表名縮寫，但必須能反映約束雙方。

四步驟自動化重命名流程

前置作業：排程在系統閒置時段執行，先完整備份應用結構與資料。

步驟 1：產生現有外鍵報告單

SELECT RPAD(child.TABLE_NAME,25,' ') Child_Tablename,
       RPAD(cp.COLUMN_NAME,17,' ')   Referring_Column,
       RPAD(parent.TABLE_NAME,25,' ') Parent_Tablename,
       RPAD(pc.COLUMN_NAME,15,' ')   Referred_Column,
       RPAD(child.CONSTRAINT_NAME,25,' ') Constraint_Name
FROM USER_CONSTRAINTS child, USER_CONSTRAINTS parent,
     USER_CONS_COLUMNS cp, USER_CONS_COLUMNS pc
WHERE child.CONSTRAINT_TYPE = 'R'
  AND child.R_CONSTRAINT_NAME = parent.CONSTRAINT_NAME
  AND child.CONSTRAINT_NAME = cp.CONSTRAINT_NAME
  AND parent.CONSTRAINT_NAME = pc.CONSTRAINT_NAME
  AND cp.POSITION = pc.POSITION
ORDER BY child.OWNER, child.TABLE_NAME, child.CONSTRAINT_NAME, cp.POSITION;

輸出 SPOOL 至檔案備查。大型系統執行較久請耐心等待。

步驟 2：產生刪除系統命名外鍵腳本

SELECT 'ALTER TABLE ' || TABLE_NAME || ' DROP CONSTRAINT ' || CONSTRAINT_NAME || ';'
FROM USER_CONSTRAINTS
WHERE CONSTRAINT_NAME LIKE 'SYS%' AND CONSTRAINT_TYPE = 'R';

輸出範例：ALTER TABLE DJ_NSRXX DROP CONSTRAINT SYS_C000231;

步驟 3：產生重建外鍵腳本（含規範名稱）

SELECT 'ALTER TABLE ' || child.TABLE_NAME || ' ADD CONSTRAINT ' ||
       'FK_' || child.TABLE_NAME || '_' || parent.TABLE_NAME || ' ' ||
       'FOREIGN KEY (' || cp.COLUMN_NAME || ') ' ||
       'REFERENCES ' || parent.TABLE_NAME || ' (' || pc.COLUMN_NAME || ')' ||
       child.DELETE_RULE || ';'
FROM USER_CONSTRAINTS child, USER_CONSTRAINTS parent,
     USER_CONS_COLUMNS cp, USER_CONS_COLUMNS pc
WHERE child.CONSTRAINT_TYPE = 'R'
  AND child.R_OWNER = parent.OWNER
  AND child.R_CONSTRAINT_NAME = parent.CONSTRAINT_NAME
  AND child.CONSTRAINT_NAME = cp.CONSTRAINT_NAME
  AND parent.CONSTRAINT_NAME = pc.CONSTRAINT_NAME
  AND cp.POSITION = pc.POSITION
  AND child.CONSTRAINT_NAME LIKE 'SYS%'
ORDER BY child.OWNER, child.TABLE_NAME, child.CONSTRAINT_NAME, cp.POSITION;

輸出範例：ALTER TABLE DJ_NSRXX ADD CONSTRAINT FK_DJ_NSRXX_DM_GY_SWRY FOREIGN KEY (RYDM) REFERENCES DM_GY_SWRY(RYDM);

人工核對：檢查生成的約束名稱是否超過 30 字元、縮寫是否清晰，必要時手工調整。

步驟 4：執行刪除→重建，驗證結果

1. 執行步驟 2 腳本刪除所有 SYS% 外鍵
2. 執行步驟 3 腳本重建規範命名外鍵
3. 重跑步驟 1 報告單對比驗證，確認無遺漏、無錯誤

擴展：複合外鍵與 DELETE_RULE 保留

腳本已包含 cp.POSITION = pc.POSITION 正確處理多欄位複合外鍵，並保留原 DELETE_RULE（CASCADE/SET NULL/NO ACTION）。若系統版本不支援 DELETE_RULE 欄位，可移除該欄位採預設行為。

外鍵命名規範化是資料庫治理的基礎建設，一次投入自動化腳本，長期受益於清晰的錯誤訊息、快速的影響分析與順暢的團隊協作。