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

Rename Oracle check constraints

This script creates a script which will rename system generated check constraints to more intelligible names.

This script will rename system-generated check constraints to more intelligible names, thus improving error re...

adability.

                
--*** This script creates a script which changes the name of 
--*** system generated check constraints 
--*** Not Null constraints are named NN_Tblname_Colname. 
--*** If generated name exceeds 30 char, it tries Tbl & Col 
--*** names without UnderScores. 
--*** If generated name still exceeds 30 char it tries Col 
--*** Position in Tbl and Tbl name. 
--*** Check constraints are named CK_Tblname_Colname. 
--*** If generated name exceeds 30 char, it tries Tbl & Col 
--*** names without UnderScores. 
--*** If generated name still exceeds 30 char it tries Col 
--*** Position in Tbl and Tbl name. 
--*** 
--*** Author: Terry Plantz  September 2001 
--*** 

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

   --*** Define cursor of Check Constraints... 
    CURSOR cons_cur 
    IS 
    SELECT C.owner, C.constraint_name, C.table_name, C.SEARCH_CONDITION 
            , TRANSLATE(C.table_name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ_',
              'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as NO_US_TableName 
      FROM dba_constraints C 
     WHERE C.owner = c_owner 
       AND C.constraint_type = 'C' 
       AND UPPER(SUBSTR(C.constraint_name,1,2)) <> 'NN' 
       --AND rownum < 200 
     ORDER BY C.table_name; 

    --*** Constraint Record Definition.... 
    cons_rec_owner          dba_constraints.owner%type; 
    cons_rec_name           dba_constraints.constraint_name%type; 
    cons_rec_table          dba_constraints.table_name%type; 
    cons_rec_no_us_table    dba_constraints.table_name%type; 
    cons_rec_condition      varchar2(2000); 

    --*** Define cursor for Check Columns.... 
    CURSOR col_curs 
    IS 
      SELECT cc.column_name,lpad(to_char(tc.column_id),3,'0') as column_id 
            , TRANSLATE(cc.column_name,'ABCDEFGHIJKLMNOPQRSTUVWXYZ_',
              'ABCDEFGHIJKLMNOPQRSTUVWXYZ') as NO_US_ColName 
        FROM dba_cons_columns cc,dba_tab_columns tc 
       WHERE cc.constraint_name = cons_rec_name 
         AND cc.owner = tc.owner 
         AND cc.table_name = tc.table_name 
         AND cc.column_name = tc.column_name; 

   v_table_name           dba_constraints.table_name%type; 
   v_checkcons        varchar2(2000); 
   v_ctr                          int; 
   v_column           VARCHAR2 (30); 
   v_no_us_column     VARCHAR2 (30); 
   v_colpos           VARCHAR2(5); 
   v_global_name      VARCHAR2 (80); 
   v_prev_global_name VARCHAR2 (80); 
   v_ColumnNumber     int; 

BEGIN 

    OPEN cons_cur; 
    v_table_name := NULL; 
    v_ctr := 0; 
    v_prev_global_name := NULL; 
   
    --*** Loop through each Check constraint.... 
    LOOP 
        FETCH cons_cur INTO cons_rec_owner,cons_rec_name,cons_rec_table,
              cons_rec_condition,cons_rec_no_us_table; 
        EXIT WHEN cons_cur%NOTFOUND; 

        v_column   := NULL; 

        --*** Obtain the Check Column and position for the 
        --*** current Check constraint... 
        FOR col_rec IN col_curs 
        LOOP 
            v_column := col_rec.column_name; 
            v_colpos := col_rec.column_id; 
            v_no_us_column := col_rec.NO_US_ColName; 
        END LOOP; 

        --*** First drop the old constraint name... 
        IF LENGTH(cons_rec_condition)=2000 THEN 
            --*** If the check condition is 2000 characters long 
            --*** chances are it was truncated so do not drop... 
            DBMS_OUTPUT.put_line ( 'Constraint too long for script 
            '|| cons_rec_owner || '.' || cons_rec_table ||  
            ' constraint ' || cons_rec_name );

        ELSE 
            DBMS_OUTPUT.put_line ( 'alter table ' || cons_rec_owner 
            || '.' || cons_rec_table ||  ' drop constraint ' 
            || cons_rec_name || ';' );

        END IF; 

       --*** Construct New Constraint Names.... 
        IF INSTR(cons_rec_condition,'IS NOT NULL') > 0 THEN 
            v_global_name := 'NN' ||'_'|| cons_rec_table||'_'|| v_column; 
            --*** If the constructed constraint name is longer 
            --*** than 30 characters, 
            --*** try using table & column names without underscores... 
            IF LENGTH(v_global_name) > 30 THEN 
                v_global_name := 'NN'||'_'|| cons_rec_no_us_table||'_'
                || v_no_us_column ; 
            END IF; 
            --*** If the constructed constraint name is still 
            --*** longer than 30 characters, 
            --*** try using column position & table name ... 
            IF LENGTH(v_global_name) > 30 THEN 
                v_global_name := 'NN'|| v_colpos ||'_'|| 
                cons_rec_no_us_table ; 
            END IF; 
        ELSE 
            v_global_name := 'CK' ||'_'|| cons_rec_table||'_'|| v_column; 
            --*** If the constructed constraint name is longer 
            --*** than 30 charaters, 
            --*** try using table & column names without underscores... 
            IF LENGTH(v_global_name) > 30 THEN 
                v_global_name := 'CK' ||'_'|| cons_rec_no_us_table||'_'
                || v_no_us_column; 
            END IF; 
            --*** If the constructed constraint name is still 
            --*** longer than 30 characters, 
            --*** try using column position & table name... 
            IF LENGTH(v_global_name) > 30 THEN 
                v_global_name := 'CK' || v_colpos ||'_'||
                  cons_rec_no_us_table ; 
            END IF; 
        END IF; 
      
        --*** Ensure the constraint name is 30 or less characters....  
        IF LENGTH(v_global_name) > 30 THEN 
            v_global_name := SUBSTR(v_global_name,1,30); 
        END IF; 

            --*** Keep a counter in case there are muliple 
            --*** constraints with the same name.... 
        IF v_global_name = v_prev_global_name THEN 
            v_ctr := v_ctr + 1; 
            v_global_name := SUBSTR(v_global_name,1,2) || TO_CHAR(v_ctr) 
            || SUBSTR(v_global_name,3,28); 
        ELSE 
                    v_ctr := 0; 
                    v_prev_global_name := v_global_name; 
        END IF; 

        DBMS_OUTPUT.put_line ( 'alter table ' || cons_rec_owner || '.' 
        || cons_rec_table ); 

        DBMS_OUTPUT.put_line ( '  add constraint ' || v_global_name 
        ||' CHECK ( ' || cons_rec_condition ||' ); ' ); 

        DBMS_OUTPUT.new_line (); 

    END LOOP; 

END; 

For More Information


This was last published in September 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close