Oracle 系統外鍵命名規範化與批次更名實作

外鍵命名規範

採用 FK_ChildTable_ParentTable 格式 (30 字元內)，可簡寫表名但必須反映雙向關係。Child Table = 受約束表 (含 FK 欄位)，Parent Table = 參照表 (含 PK/UK)。

四步驟批次更名法

1. 產生現狀報表

SELECT RPAD(child.TABLE_NAME,25) Child_Table,
       RPAD(cp.COLUMN_NAME,17) Ref_Col,
       RPAD(parent.TABLE_NAME,25) Parent_Table,
       RPAD(pc.COLUMN_NAME,15) Refd_Col,
       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. 產生刪除系統自動命名 (SYS_%) 外鍵腳本

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

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;

若表名過長須手動調整 FK 名稱長度。

4. 執行與驗證

1. 執行步驟 2 刪除腳本。
2. 執行步驟 3 建立腳本。
3. 重跑步驟 1 報表對比驗證。

注意： 操作耗時長，需在維護窗口執行；涉及 DDL，建議先完整備份。