Problem solve Get help with specific problems with your technologies, process and projects.

Renaming foreign keys

A script that renames foreign keys from system-assigned constraint names to more intelligible names.

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.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close