RE: naming conventions (constraints and indexes)

From: <>
Date: Thu, 10 Nov 2011 10:36:57 -0500
Message-ID: <>

I worked somewhere where they did something similar as well. Essentially what you have described is abbreviated names with rules.

I let them use descriptive names for tables and columns with no suffixes or prefixes (it can get complicated with multiple apps if you've gone there).

Our rules come in with the constraints, indexes. I let the developers create the names, but sometimes the time comes when a few get in and its time to clean up.

So for the 30 character limit, One thing that can be done is, 'for instance', with Null constraints is this small script below. It will take up to 10 different tries to create a unique name using rules.

I have variations of this for different reasons and tasks.


  • alter table physical_flights rename constraint abc to PF_CKC_FLIGHT_STAT;

set serveroutput on size 1000000
set feedback off
set pagesize 200
set linesize 2000
set trim on
set heading off
set verify off
set scan on
set echo off

column search_condition format a100
column v_username new_value dsp_username column v_dbname new_value dsp_dbname

set termout off
select user v_username,

       decode(instr(global_name,'.'),0,global_name,substr(global_name,1,instr(global_name,'.')-1)) v_dbname
       from global_name;

set termout on

spool 1_rename_cons_nn_dynamic_&&dsp_username._&&dsp_dbname..lis declare

  • *****************************************************************************
  • * Script: 1_rename_all_cons_nn_dynamic.SQL *
  • * Author: Joel patterson *
  • * Usage: run it *
  • * Function: rename not null constraints, *
  • * This script immediately renames all not null constraints for *
  • * the current user. By renaming immediate after the new constraint name has
  • * been decided, allows for duplicates to be discarded in the exception
  • * section. other wise the second or third duplication would have the same
  • * Example: if three constraints would generate the name table_column_nn
  • * and you spooled to a file, the constraint already exists error would
  • * generate for the second and third. However in this script an exception
  • * would catch the error and a new name would be generated.
  • ***************************************************************************** name_already_used EXCEPTION; pragma exception_init(name_already_used, -02264); t_txt varchar2(255); t_search varchar2(2000); l_cnt number; t_cons varchar2(30); finished boolean; t_tbl_nam varchar2(30); t_col_nam varchar2(30); isnullable char(1);

  cursor c_constraints is

             c.STATUS           STATUS,
             c.table_name       TABLE_NAME,
             cc.column_name     COLUMN_NAME
        from user_CONSTRAINTS c, user_CONS_COLUMNS cc
        and c.CONSTRAINT_TYPE = 'C'
      order by c.table_name, c.constraint_name;

BEGIN   FOR c_con in c_constraints LOOP
      t_search := trim(replace(c_con.SEARCH_CONDITION,chr(10),' '));

      IF (instr(t_search,'IS NOT NULL') > 0)  THEN
        -- If old constraint name is already the same as new composed name then leave alone.
        if (c_con.constraint_name = substr(c_con.table_name,1,10) ||'_'|| substr(c_con.column_name,1,14)||'_NN') THEN
          t_tbl_nam := c_con.table_name;
          t_col_nam := c_con.column_name;
          select nullable into isnullable 
            from user_tab_columns
            where table_name = t_tbl_nam
              and column_name = t_col_nam;
          IF isnullable = 'N' THEN
            t_txt := 'ALTER TABLE "'||c_con.table_name||'" RENAME CONSTRAINT '||c_con.constraint_name||' to ';
            t_txt := t_txt || substr(c_con.table_name,1,10) ||'_'|| substr(c_con.column_name,1,14)||'_NN;';
            t_txt := substr(t_txt,1,length(t_txt)-1);
            execute immediate t_txt;
          END IF;
        END IF;
      END IF;

      WHEN name_already_used THEN
        dbms_output.put_line('rem EXCEPTION');
        finished := FALSE;

        -- if the new composed name already exists try and change it again up to 9 times.
        For i in 1..9 LOOP
          IF not finished THEN
            t_cons := substr(c_con.table_name,1,10-i) ||'_'|| substr(c_con.column_name,1,14+i)||'_NN';
            select count(*) into l_cnt from user_constraints where constraint_name = t_cons;
            IF l_cnt = 0 THEN
              t_txt := 'ALTER TABLE "'||c_con.table_name||'" RENAME CONSTRAINT '||c_con.constraint_name||' to ';
              t_txt := t_txt || t_cons;
              execute immediate t_txt;
              finished := TRUE;
            END IF;
          END IF;
        END LOOP;


prompt prompt

prompt prompt *************************************************************************
prompt prompt OUTPUT spooled to 1_rename_cons_nn_dynamic_&&dsp_username._&&dsp_dbname..lis
prompt prompt *************************************************************************
prompt prompt

spool off
set feedback on
set heading on
undefine v_dbname
undefine v_username
undefine dsp_dbname
undefine dsp_username

Joel Patterson
Database Administrator
904 727-2546

-----Original Message-----
From: Herring Dave - dherri [] Sent: Thursday, November 10, 2011 9:28 AM To: Patterson, Joel; Subject: RE: naming conventions (constraints and indexes)

We've used various unique suffixes to identify the different object types, similar to what you listed, but what I've found is problems with the base name. What happens when you have a long table name, such as ACCOUNT_TRANSACTION_HISTORY_TB. Ignore trying to come up with a better table name for now as the example is contrived. I was on a project in a previous company where they decided than any dependent objects would use a 4-character abbreviation off the table name, using the first initial of each "word" in the table name. If the table has only 1 word then use the first 4 characters of that word. If it has 2 words then use 2 characters of each word, etc.

So in this example all objects based on ACCOUNT_TRANSACTION_HISTORY_TB would use ACTH to identify the object it's based on. Indexes would be named like "ACTH_<col abbrev>_PK", having a similar convention for columns involved. A PK on ACCOUNT_ID and ACCOUNT_DT would be named "ACTH_AI_AD_PK". We had rules to deal with conflicts, pretty much any issue, but I won't bore you all with those details.

Incredibly cryptic? Yup, but we never had issues with running out of room for object names, followed the same logic for everything and were able to nearly include all important info on object names to help make those names have all key info. Eventually you get use to it and automatically decrypt without issue.

Take it for just another example of what others have done. This example was from a HIGHLY normalized OLTP db. We also had (and maintained) conceptual, logical, and physical data models, which I haven't seen since on any project.

Acxiom Corporation

TEL    630.944.4762
MBL   630.430.5988 

1501 Opus Pl, Downers Grove, IL 60515, USA WWW.ACXIOM.COM The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

-----Original Message-----
From: [] On Behalf Of Sent: Wednesday, November 09, 2011 1:18 PM To:
Subject: naming conventions (constraints and indexes)

Its an art and a science isn't it. I like suffixes for some things, and prefixes for others. I'm limiting my question to constraints and associated indexes and only the suffixes. I use suffixes for indexes. (Just to satisfy curiosity, I use prefixes for things like sequences S_, functions F_, package bodies P_ and packages, views V_, Procedures _R etc).

Constraints have suffixes such as _FK, _CK..., so _UK, and _PK. I notice that it is easy to create a primary or unique constraint with an index of the same name even with the using index clause. The result is an index with suffix _UK, and PK.

However, I was wondering what some opinions are about giving extra attention to (developers, modelers etc) to separate further these types. So all constraints are 'K' and all indexes 'X'. e.g. ( FK, FX), (PK, PX), (UK, UX).

An immediate result that I see is that FK would not need the FK_I , (or even a number for multiples, as it would be simply end in _FX. (NAME_FK, NAME_FX).

The idea of further refining this is somewhat a result of what Tom Kyte says about metadata and indexes, and that metadata is good.

Examples concerning metadata:
A constraint is metadata, an index is the mechanism. A foreign key can be defined to a unique constraint, but not to a unique index.

This of course takes some extra effort. I think as things get bigger that it is worth it. Any consensus out there?

Example: (leaving out FK).

  PRIM_COL number constraint EXAMPLE_TABLE_PK primary key     using index ( CREATE INDEX EXAMPLE_TABLE_PX ON       EXAMPLE_TABLE(PRIM_COL)),
  UNIQ_COL number constraint EXAMPLE_TABLE_UNIQ_COL_UK UNIQUE     using index ( create index EXAMPLE_TABLE_UNIQ_COL_UX on       EXAMPLE_TABLE(UNIQ_COL)),
  junk varchar2(10)

select table_name, constraint_name, constraint_type, index_name

      from user_constraints where table_name = 'EXAMPLE_TABLE';

------------------------- ------------------------- ------------------

DBMON _at_ COSDEV> select table_name, index_name from user_indexes where table_name = 'EXAMPLE_TABL

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------

Joel Patterson
Database Administrator
904 727-2546

Received on Thu Nov 10 2011 - 09:36:57 CST

Original text of this message