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 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
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>



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

Other related posts: