RE: 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. Nothing for Tables).

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

--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 09 2011 - 13:21:23 CST