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

CREATE TABLE EXAMPLE_TABLE (
  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';

TABLE_NAME                CONSTRAINT_NAME           C INDEX_NAME
------------------------- ------------------------- ------------------
EXAMPLE_TABLE             EXAMPLE_TABLE_PK          P EXAMPLE_TABLE_PX
EXAMPLE_TABLE             EXAMPLE_TABLE_UNIQ_COL_UK U EXAMPLE_TABLE_UNIQ_COL_UX

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

TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
EXAMPLE_TABLE                  EXAMPLE_TABLE_PX
EXAMPLE_TABLE                  EXAMPLE_TABLE_UNIQ_COL_UX


Joel Patterson
Database Administrator
904 727-2546



Joel Patterson
Database Administrator
904 727-2546



--
http://www.freelists.org/webpage/oracle-l

Other related posts: