Hi,

I am currently doing a review of my personal database naming
conventions and found that there are not much (good) examples in the
literature for some special objects. Certainly there are bigger issues
in a database project that that, but I would like to try cleaning that
up.

I am using SQL Server 2005 what requires that constraint names are
unique in the whole database, index names to be unique on table level
at least. I do NOT wish to use any prefixes or suffixes such as PK,
FK, CK, UQ, DF, Trig and so on.

As this however is not a SQL Server specific problem I also appreciate
input from those who do not use this system.

1) Triggers
I am using <action>_<table name> format e. g. AfterInsert_Orders,
InsteadOfInsert_Orders for multi-purposed triggers. If a more
meaningfull name can be found, such as CheckManagers_Employees when a
trigger is used to replace ASSERTION statements, I use this in the
form of <verb><object>_<table name>.

2) Check constraints
This is easy stuff, once triggers are single purposed. I use
<explanation>_<table name> (e. g. PreventSingleNodes_Relations or
ValidateRelationType_Relations)

3) Default constraints
Here it starts to become ugly: Default_<column name>_<table name> and
DummyDefault_<column name>_<table name> for columns which have dummy
values as default such as 'XXXX'. Better ideas here? I did not find
any beautiful solution for this.

4) Unique constraints
More ugly things: Unique_<column names>_<table name>. Better ideas
here?

5) Primary key constraints
PrimaryKey_<table name>. How to name a primary key if not primary key?

6) Foreign key constraints
This is really messy: Using Relation_<referencing table>_<referencing
column>_<referenced table>_<referenced column> stops working when
compound keys are involved and adding all these column names would
result in very long names. An alternative may be Relation_<referencing
table>_<referenced table>_< column names> as I use the same column
names for the same data elements, so the columns which reference each
other have in 99 % of cases the same name - BUT there are exceptions
when an alias name is involved (think at a 'Projects' table with
'EmployeeID' and 'ManagerID' as an alias column name). Any better
ideas are much appreciated.

7) Indexes
As SQL Server does not require unique index names in the whole
database but just in the table, it could be simply <column names>. An
alternative might be Index_<column names>_<table name> to ensure
consistency with the other conventions. For the primary key SQL Server
creates the same name for the clustered index (if there is one
assigned), what is not changeable. Renaming the index for the primary
key also renames the primary key constraint. - As well here, better
suggestions welcome.

Thanks in advance for your replies.

Brgds

Philipp Post

Have you looked at the ISO-11179 Standards? I happen to have a book
on this topic, SQL PROGRAMMING STYLE, based on the Standards and few
decades of research in code readability.


Actually, formatting and style can reduce the cost of maintaining code
(where 80%+ of the total lifetime cost is) by 8-12%.

FK, CK, UQ, DF, Trig and so on. <<

Standard SQL requires that constraint names are unique in the schema.
And you got the affix thing right --name something for what it is and
not for how it is used in one place.

Very good and ISO conformant.

For table level constraints okay; but at the column level you might
want the data element involved

That is because defaults and uniques are really part of the domain of
the attribute rather than a constraint on it

change much so I don't need to reference them. If there is an error,
then the message includes that they are PK or FK, so the constraint
name adds no information.

Standard SQL has no physical access methods, since that is considered
implementation and not language definition. I am not sure if other
products have global or local index (hash, bit vector, etc.) names. I
agree that having the table and column names will add information to
the error messages.

Have you looked at the ISO-11179 Standards? I happen to have a book
on this topic, SQL PROGRAMMING STYLE, based on the Standards and few
decades of research in code readability.


Actually, formatting and style can reduce the cost of maintaining code
(where 80%+ of the total lifetime cost is) by 8-12%.

FK, CK, UQ, DF, Trig and so on. <<

Standard SQL requires that constraint names are unique in the schema.
And you got the affix thing right --name something for what it is and
not for how it is used in one place.

Very good and ISO conformant.

For table level constraints okay; but at the column level you might
want the data element involved

That is because defaults and uniques are really part of the domain of
the attribute rather than a constraint on it

change much so I don't need to reference them. If there is an error,
then the message includes that they are PK or FK, so the constraint
name adds no information.

Standard SQL has no physical access methods, since that is considered
implementation and not language definition. I am not sure if other
products have global or local index (hash, bit vector, etc.) names. I
agree that having the table and column names will add information to
the error messages.

Have you looked at the ISO-11179 Standards? I happen to have a book
on this topic, SQL PROGRAMMING STYLE, based on the Standards and few
decades of research in code readability.


Actually, formatting and style can reduce the cost of maintaining code
(where 80%+ of the total lifetime cost is) by 8-12%.

FK, CK, UQ, DF, Trig and so on. <<

Standard SQL requires that constraint names are unique in the schema.
And you got the affix thing right --name something for what it is and
not for how it is used in one place.

Very good and ISO conformant.

For table level constraints okay; but at the column level you might
want the data element involved

That is because defaults and uniques are really part of the domain of
the attribute rather than a constraint on it

change much so I don't need to reference them. If there is an error,
then the message includes that they are PK or FK, so the constraint
name adds no information.

Standard SQL has no physical access methods, since that is considered
implementation and not language definition. I am not sure if other
products have global or local index (hash, bit vector, etc.) names. I
agree that having the table and column names will add information to
the error messages.

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

(SNIP)

Obviously you meant "I tend not to put a constraint name on them at all."
(My personal preference as well.)

Aren't you assuming that all DBMS vendors will provide adequate PK/FK info
(without the constraint name) when an error occurs? There could be multiple
FK columns on a table which reference the same PK table (e.g., a PK table
of valid states or countries).

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

(SNIP)

Obviously you meant "I tend not to put a constraint name on them at all."
(My personal preference as well.)

Aren't you assuming that all DBMS vendors will provide adequate PK/FK info
(without the constraint name) when an error occurs? There could be multiple
FK columns on a table which reference the same PK table (e.g., a PK table
of valid states or countries).

"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

(SNIP)

Obviously you meant "I tend not to put a constraint name on them at all."
(My personal preference as well.)

Aren't you assuming that all DBMS vendors will provide adequate PK/FK info
(without the constraint name) when an error occurs? There could be multiple
FK columns on a table which reference the same PK table (e.g., a PK table
of valid states or countries).

Yes; the table name and the PRIMARY KEY violation should be in the
exception message and SQLSTATE code.

But each of the FK columns will have a different column name which
shows the role being played by the data element, which should be in
the error message and SQLSTATE code. If I reference Personnel(emp_id)
twice in a table4, then one of them might "mgr_emp_id" and
"assistant_emp_id" -- the role each one plays shown in their prefix.

Yes; the table name and the PRIMARY KEY violation should be in the
exception message and SQLSTATE code.

But each of the FK columns will have a different column name which
shows the role being played by the data element, which should be in
the error message and SQLSTATE code. If I reference Personnel(emp_id)
twice in a table4, then one of them might "mgr_emp_id" and
"assistant_emp_id" -- the role each one plays shown in their prefix.

Yes; the table name and the PRIMARY KEY violation should be in the
exception message and SQLSTATE code.

But each of the FK columns will have a different column name which
shows the role being played by the data element, which should be in
the error message and SQLSTATE code. If I reference Personnel(emp_id)
twice in a table4, then one of them might "mgr_emp_id" and
"assistant_emp_id" -- the role each one plays shown in their prefix.