RE: naming conventions (constraints and indexes)
Date: Thu, 10 Nov 2011 08:43:14 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8A2E2364_at_JAXMSG01.crowley.com>
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_at_roughsea.com] Sent: Wednesday, November 09, 2011 5:03 PM To: oracle-l_at_freelists.org; 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_at_crowley.com<mailto:Joel.Patterson_at_crowley.com> 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 _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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 10 2011 - 07:43:14 CST