RE: FW: rename foreign keys from system-assigned constraint names

Hello Jared

I tested it. Seems to work ok on 8.1.6 on NT. You will need to define how to recognize system assign constraint names. One bug found: The resulting constraint name can be longer then 32 letters if you tables have long names.
Maybe you can use another method to determine the resulting constraint name.

Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il

> -----Original Message-----
> From: Jared.Still_at_radisys.com [SMTP:Jared.Still_at_radisys.com]
> Sent: Tue, February 19, 2002 7:59 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: FW: rename foreign keys from system-assigned constraint
> names to
>
> Have you or anyone else on the list used this script?
>
> Successfully? More than once?
>
> Jared
>
>
>
>
>
> YTTRI Lisa <lisa.yttri_at_cnh.com>
> Sent by: root_at_fatcity.com
> 02/19/02 08:43 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <ORACLE-L_at_fatcity.com>
> cc:
> Subject: FW: rename foreign keys from system-assigned
> constraint names to
>
>
> Here is something that was posted before - I haven't tried it so I don't
> know how well it works (if it does).
>
> -----Original Message-----
> Sent: Wednesday, August 08, 2001 4:23 PM
> To: Multiple recipients of list ORACLE-L
> more
>
>
> 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:
> INET: Jared.Still_at_radisys.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
  INET: adary_at_mehish.co.il

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).