naming conventions (constraints and indexes)
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Nov 2011 14:18:09 -0500
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
