RE: naming conventions (constraints and indexes)

I worked somewhere where they did something similar as well. Essentially what you have described is abbreviated names with rules.

I let them use descriptive names for tables and columns with no suffixes or prefixes (it can get complicated with multiple apps if you've gone there).

Our rules come in with the constraints, indexes. I let the developers create the names, but sometimes the time comes when a few get in and its time to clean up.

So for the 30 character limit, One thing that can be done is, 'for instance', with Null constraints is this small script below. It will take up to 10 different tries to create a unique name using rules.

I have variations of this for different reasons and tasks.

WARNING: THIS SCRIPT WILL RENAME THE CONSTRAINTS WITHOUT ASKING AGAIN.

We've used various unique suffixes to identify the different object types, similar to what you listed, but what I've found is problems with the base name. What happens when you have a long table name, such as ACCOUNT_TRANSACTION_HISTORY_TB. Ignore trying to come up with a better table name for now as the example is contrived. I was on a project in a previous company where they decided than any dependent objects would use a 4-character abbreviation off the table name, using the first initial of each "word" in the table name. If the table has only 1 word then use the first 4 characters of that word. If it has 2 words then use 2 characters of each word, etc.

So in this example all objects based on ACCOUNT_TRANSACTION_HISTORY_TB would use ACTH to identify the object it's based on. Indexes would be named like "ACTH_<col abbrev>_PK", having a similar convention for columns involved. A PK on ACCOUNT_ID and ACCOUNT_DT would be named "ACTH_AI_AD_PK". We had rules to deal with conflicts, pretty much any issue, but I won't bore you all with those details.

Incredibly cryptic? Yup, but we never had issues with running out of room for object names, followed the same logic for everything and were able to nearly include all important info on object names to help make those names have all key info. Eventually you get use to it and automatically decrypt without issue.

Take it for just another example of what others have done. This example was from a HIGHLY normalized OLTP db. We also had (and maintained) conceptual, logical, and physical data models, which I haven't seen since on any project.

DAVID HERRING
DBA
Acxiom Corporation

EML dave.herring_at_acxiom.com
TEL 630.944.4762
MBL 630.430.5988

1501 Opus Pl, Downers Grove, IL 60515, USA
WWW.ACXIOM.COM
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system. Thank you.

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?