Renaming foreign keys

• Terry Plantz
• Published: 08 Aug 2001

Here is a script that renames foreign keys from system-assigned constraint names to more intelligible names. The names use the current table name and the referenced table name. This greatly improves readability, especially for error messages.

DECLARE 
   c_owner   CONSTANT VARCHAR2 (30)    := 'FLEETPRO'; 

   CURSOR cons_cur 
   IS 
      SELECT C.owner, C.constraint_name, C.table_name, 
      TRANSLATE (C.table_name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ_',
                 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')
      as RTrimTable

     , CR.owner as ROwner, CR.constraint_name as RCName, 
       CR.table_name as RTable, 
       TRANSLATE (CR.table_name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ_',
                  'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as RtrimRTable

      FROM all_constraints C, all_constraints CR 
      WHERE C.owner = 'FLEETPRO' 
        AND C.owner = CR.owner 
        AND C.constraint_type in ('R') 
        AND UPPER(SUBSTR(C.constraint_name,1,3)) <> 'FK_' 
        AND C.r_constraint_name = CR.constraint_name 
                ORDER BY C.table_name,CR.table_name; 
--              AND rownum < 50; 

   cons_rec           cons_cur%ROWTYPE; 

   CURSOR col_curs 
   IS 
      SELECT column_name, position 
        FROM all_cons_columns cc 
       WHERE cc.owner = cons_rec.owner 
         AND cc.constraint_name = cons_rec.constraint_name 
         AND cc.table_name = cons_rec.table_name 
       ORDER BY position; 

   CURSOR col_R_curs 
   IS 
      SELECT column_name, position 
        FROM all_cons_columns cc 
       WHERE cc.owner = cons_rec.ROwner 
         AND cc.constraint_name = cons_rec.RCname 
         AND cc.table_name = cons_rec.RTable 
       ORDER BY position; 

   v_table_name           all_constraints.table_name%type; 
   v_Rtable_name          all_constraints.table_name%type; 
   v_ctr                          int; 
    
   v_fklist           VARCHAR2 (1000); 
   v_fklist_R         VARCHAR2 (1000); 
   v_global_name      VARCHAR2 (80); 
BEGIN 
   OPEN cons_cur; 
   v_table_name := NULL; 
   v_Rtable_name := NULL; 
   v_ctr := 0; 
   
   LOOP 
      FETCH cons_cur INTO cons_rec; 
      EXIT WHEN cons_cur%NOTFOUND; 

      v_fklist   := NULL; 
      v_fklist_R := NULL; 

      FOR col_rec IN col_curs 
      LOOP 
         IF v_fklist IS NULL THEN 
            v_fklist := '( ' || col_rec.column_name; 
         ELSE 
            v_fklist := v_fklist || ', ' || col_rec.column_name; 
         END IF; 
      END LOOP; 

      FOR col_rec IN col_R_curs 
      LOOP 
         IF v_fklist_R IS NULL THEN 
            v_fklist_R := '( ' || col_rec.column_name; 
         ELSE 
            v_fklist_R := v_fklist_R || ', ' || col_rec.column_name; 
         END IF; 
      END LOOP; 

          
          IF ((v_table_name = cons_rec.table_name) 
           AND (v_Rtable_name = cons_rec.Rtable)) 
           THEN v_ctr := v_ctr + 1; 
          Else 
                  v_ctr := 0; 
                  v_table_name := cons_rec.table_name; 
                  v_Rtable_name := cons_rec.Rtable; 
          End If; 
          
          v_fklist   := v_fklist || ')'; 
      v_fklist_R := v_fklist_R || ')'; 
          
      DBMS_OUTPUT.put_line ( 
         'alter table ' || cons_rec.owner || '.' || cons_rec.table_name 
      ); 
      DBMS_OUTPUT.put_line ( 
         'drop constraint ' || cons_rec.constraint_name || ';' 
      ); 
      DBMS_OUTPUT.put_line ( 
         'alter table ' || cons_rec.owner || '.' || cons_rec.table_name 
      ); 
          IF v_ctr > 0 THEN 
                v_global_name := 'FK_' || cons_rec.table_name || '_' 
                || cons_rec.RTable || to_char(v_ctr); 
          ELSE 
                v_global_name := 'FK_' || cons_rec.table_name || '_' 
                || cons_rec.RTable; 
          END IF; 
      IF length( v_global_name ) > 29 Then 
                  IF v_ctr > 0 then 
                v_global_name := 'FK_' || cons_rec.RtrimTable || '_' 
                || cons_rec.RtrimRTable || to_char(v_ctr); 
                  ELSE 
                v_global_name := 'FK_' || cons_rec.RtrimTable || '_' 
                || cons_rec.RtrimRTable; 
                  END IF; 
      END IF; 
      
      DBMS_OUTPUT.put_line ( 
         'add constraint ' || v_global_name || ' foreign key ' || 
            v_fklist 
      ); 
      DBMS_OUTPUT.put_line ( 
         'references ' || cons_rec.ROwner || '.' || cons_rec.Rtable || ' ' || 
            v_fklist_R || 
            ';' 
      ); 



      DBMS_OUTPUT.new_line (); 
   END LOOP; 
END; 

For More Information

• What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBA's? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, and DB2 gurus are waiting to answer your toughest questions.