Oracle系統中表外鍵的更名
2024-08-29 13:46:31
供稿：網友
Oracle中表的外鍵是保證系統參照完整性的手段，而參照完整性是指分布在兩個表中的列所滿足的具有主從性質的約束關系。外鍵涉及到兩個表，其中一個稱之為父項表，另一個稱之為子項表。
父項表（parent table）是參照約束的基礎，即通過檢查這張表的有效數據情況來判定約束是否成立，它是參照約束的條件，影響約束，而不受約束的任何影響。
子項表（child table）是參照約束的對象，當其發生變化，如有新數據輸入時，通過比較父項表中的有效數據狀況，來判定這些變化是否符合約束條件，若不符合，則拒絕要發生的變化。
在實際應用系統中，開發者為了保證系統的完整性，一般要定義大量的外鍵。然而，假如外鍵的命名不規范，如采用系統自動生成的名稱，則在以后的系統運行維護中會造成很大的麻煩。如在系統運行后，加載大量數據或者進行一些數據轉換操作等時，出現外鍵錯誤時，根據系統提示的外鍵錯誤，根本不可能直接定位到那兩個表間的外鍵發生錯誤，需要浪費很多時間查找造成錯誤發生的外鍵的父項表和子項表，然后才能進一步確定是那條記錄違反了外鍵約束條件。通常，我們采用這樣的命名規則來命名外鍵FK_Child_table name_Parent_table name。由于外鍵名稱的最大長度限制在30個字符之內，對child_table_name和Parent_table name不一定和原表一模一樣，可以采取一些簡寫的辦法，但名稱一定要能反映出約束的兩個表。這里的命名，Child_table name指子項表，也就是約束表，Parent_table name是指父項表，也就是被約束的表。
下面來具體討論如何將應用系統中不規范的外鍵命名修改為規范的外鍵名稱。在討論之前，需要提醒讀者注重的是，完成下面的操作需要花費較長的時間，所以一定要規劃在系統空閑時來完成。同時這里的外鍵更名，采用的方法是首先刪除然后重建，涉及到刪除應用系統對象的操作，所以在操作之前，為安全起見，應該備份應用系統。
一、生成系統目前的外鍵報告單
首先生成系統目前模式下的所有外鍵情況報告單，SQL腳本如下：
******************************************************************************
腳本1：列出當前模式下所有外鍵的報告表，可以將其spool到某個文件中
******************************************************************************/
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;
該腳本生成所在模式下的所有外鍵情況，包括外鍵名稱，父項表名稱，子項表名稱以及引用的列名稱等。在SQL/PLUS下運行該腳本，在運行該腳本之前，可以將輸出SPOOL到本地某個文件中。同時要注重，假如應用系統中的外鍵比較多且復雜的話，這個腳本的運行時間會比較長。
二、生成刪除系統自動命名的外鍵腳本
在SQL/PLUS下，運行下面的腳本來生成刪除系統自動命名（也就是外鍵名稱以SYS為前綴）的所有外鍵，和生成外鍵報告一樣，將生成腳本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; 三、生成重新創建刪除的外鍵腳本
在SQL/PLUS下，運行下面的腳本來生成重新創建刪除的外鍵腳本：
******************************************************************************
腳本3：重新創建外鍵
******************************************************************************/
SELECT 'ALTER TABLE ' child.TABLE_NAME' '
'ADD CONSTRAINT ' '外鍵名稱 ' ' '
'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 外鍵名稱 FOREIGN KEY (RYDM) REFERENCES DM_GY_SWRY(RYDM);
將上面的外鍵名稱以上面介紹的規范命名規則命名的外鍵名稱代替，就是
ALTER TABLE DJ_NSRXX ADD CONSTRAINT FK_DJ_NSRXX_DM_GY_SWRY FOREIGN KEY (RYDM) REFERENCES DM_GY_SWRY(RYDM);
四、外鍵更名
生成上面的兩個腳本后，首先運行第二步中刪除系統自動生成外鍵的腳本，將系統中命名不規范的外鍵刪除，然后運行第三步中生成的創建外鍵的腳本，重新創建這些刪除的外鍵，也就實現了對不規范外鍵的更名。
五、系統檢查
操作完成后，重新執行步驟1，再生成一個應用系統的外鍵報告單，作對比檢查。假如正確無誤，則更名成功，否則查找原因。
