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 [email protected] 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.