rename foreign keys from system-assigned constraint names to more

From: Glenn Travis <c-glenn.travis_at_wcom.com>
Date: Wed, 08 Aug 2001 13:09:33 -0700
Message-ID: <F001.0036508D.20010808132233@fatcity.com>

I just found this script on another site and thought this group would benefit from it, as we are always looking for usefult scripts... apologies if the formatting is bad.

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;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Glenn Travis
  INET: c-glenn.travis_at_wcom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Aug 08 2001 - 15:09:33 CDT