萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 外鍵重命名規範與自動化腳本:從 SYS_ 系統命名到 FK_子表_父表 規範名稱

為何要重命名

系統自動生成名稱 (如 SYS_C00123) 無語意,發生 ORA-02291 父鍵不存在錯誤時,無法快速定位涉及哪兩張表、哪幾個欄位,導致排查時間從分鐘拉長到小時。

命名規範

FK_子表簡稱_父表簡稱 (≤30 字元)
例:FK_ORD_CUST (ORDERS → CUSTOMERS)

  • 子表 = 受約束表 (含外鍵欄位)
  • 父表 = 被參照表 (含主鍵/唯一鍵)
  • 簡稱採業務通用縮寫,必要時加序號區分多欄位外鍵。

自動化三步驟

步驟 1:產出現狀報告

SET LINES 200 PAGES 1000
COL child_table FORMAT A25
COL parent_table FORMAT A25
COL child_col FORMAT A17
COL parent_col FORMAT A15
COL constraint_name FORMAT A25

SELECT RPAD(cchild.table_name,25) child_table,
       RPAD(ccol.column_name,17) child_col,
       RPAD(pparent.table_name,25) parent_table,
       RPAD(pcol.column_name,15) parent_col,
       RPAD(cchild.constraint_name,25) constraint_name
FROM   user_constraints cchild
JOIN   user_constraints pparent ON cchild.r_constraint_name = pparent.constraint_name
JOIN   user_cons_columns ccol ON cchild.constraint_name = ccol.constraint_name
JOIN   user_cons_columns pcol ON pparent.constraint_name = pcol.constraint_name
                            AND ccol.position = pcol.position
WHERE  cchild.constraint_type = 'R'
ORDER BY cchild.table_name, cchild.constraint_name, ccol.position;

輸出導向檔案 fk_report.lst 供審核。

步驟 2:產生 DROP 腳本 (針對 SYS_ 開頭)

SET HEAD OFF FEED OFF PAGES 0
SPOOL drop_fk.sql
SELECT 'ALTER TABLE '||table_name||' DROP CONSTRAINT '||constraint_name||';'
FROM   user_constraints
WHERE  constraint_type = 'R'
AND    constraint_name LIKE 'SYS_%';
SPOOL OFF

審核 drop_fk.sql 確認無誤後執行。

步驟 3:產生 CREATE 腳本 (依規範命名)

-- 需結合步驟1報告人工編寫或寫 PL/SQL 動態生成
-- 範例:
ALTER TABLE orders ADD CONSTRAINT fk_ord_cust
  FOREIGN KEY (cust_id) REFERENCES customers(cust_id) ENABLE;

建議在維護窗口執行,**操作前必備份** (含結構與數據)。

驗證

SELECT constraint_name, table_name, status
FROM user_constraints
WHERE constraint_type='R'
ORDER BY table_name;

確認所有外鍵 STATUS=ENABLED 且名稱符合規範。

延伸:主鍵/唯一鍵同理可重命名

但**極不建議**停用 PK/UK 進行資料維護,若必須,請用 DISABLE NOVALIDATE 後再 ENABLE VALIDATE,並嚴格控制窗口。

copyright © 萬盛學電腦網 all rights reserved