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

                
--*** 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

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