RE: naming conventions (constraints and indexes)
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <sfaroult@xxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 Nov 2011 08:43:14 -0500
Thanks Stephane, I make up those names for this example. I was going for the
suffix portion of the names. We already have naming conventions, but I have
been letting the constraint name and index name be identical. But I am
looking for feedback on changing the suffix (PK,PX) (UK,UX) (FK,FX) ....
Joel Patterson
Database Administrator
904 727-2546
From: Stephane Faroult [mailto:sfaroult@xxxxxxxxxxxx]
Sent: Wednesday, November 09, 2011 5:03 PM
To: oracle-l@xxxxxxxxxxxxx; Patterson, Joel
Subject: Re: naming conventions (constraints and indexes)
Joel,
Concerning constraint naming, I am currently experimenting with giving
"user-friendlyl" names to constraints - for instance, instead of
EXAMPLE_TABLE_UNIQ_COL_UK as in your example, naming the constraint "UNIQ_COL
must be unique", or, instead of EXAMPLE_TABLE_FLAG_CK, "FLAG must be Y or N".
The idea is to have a hint about what went wrong returned to the user, courtesy
of the error message that usually gives the name of the constraint that was
violated. Of course, if no "raw" error message is ever returned to the user,
it's not so important. The user will just call support saying that a window
displayed "database error" on his/her screen.
Needless to say, the 30-character Oracle limit is a problem (better with a DBMS
that takes identifiers that can be 64 or 128 character long), and the unicity
of constraint names in the namespace requires extensive creativity if you have
tons of tables with an ACTIVE flag that can be Y or N.
But it some cases, it can improve the "user experience" (I love this one).
HTH
--
Stephane Faroult
RoughSea Ltd<http://www.roughsea.com>
Konagora<http://www.konagora.com>
RoughSea Channel on Youtube<http://www.youtube.com/user/roughsealtd>
On 11/09/2011 08:21 PM,
Joel.Patterson@xxxxxxxxxxx<mailto:Joel.Patterson@xxxxxxxxxxx> wrote:
Resent.
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 @ 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
