Re: naming conventions (constraints and indexes)
From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Wed, 09 Nov 2011 23:02:43 +0100
Message-ID: <4EBAF883.7030209_at_roughsea.com>
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.
Date: Wed, 09 Nov 2011 23:02:43 +0100
Message-ID: <4EBAF883.7030209_at_roughsea.com>
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 wrote:Received on Wed Nov 09 2011 - 16:02:43 CST
> 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