Re: Naming conventions for special database objects

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. <<

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.

Certainly there are bigger issues in a database project that that, but I would like to try cleaning that
up. <<

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

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. <<

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.

1) Triggers .. <verb><object>_<table name>. <<

Very good and ISO conformant.

2) Check constraints: I use <explanation>_<table name> (e. g. PreventSingleNodes_Relations or ValidateRelationType_Relations) <<

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

3) Default constraints, Unique constraints .. Better ideas here?

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

5) Primary key constraints, Foreign key constraints ..<<
I tend not to put a constraint on them at all. Their nature does not
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.

7) Indexes: As SQL Server does not require unique index names in the whole database but just in the table, <<

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.
.

Relevant Pages

  • Re: DEFAULT Wert nachträglich über SQL ändern
    ... An welche Standards sollte sich Access denn halten? ... Wenn etwas vom SQL Server nicht ... > verwendet gibt es in Access leider einen Fehler wegen der CONSTRAINT ...
    (microsoft.public.de.access)
  • Re: MS SQL Server - a plethora of limitations...
    ... > Most of your objections are that MS-SQL is not Oracle. ... according to the standards. ... > There is no such syntax or concept in Standard SQL. ... Partitioning shouldn't be done automatically by the DB, ...
    (microsoft.public.sqlserver.programming)
  • Re: How do i write Set based queries and avoid a cursor?
    ... certain coding standards, which also may predate certain ANSI/ISO ... since this varies so greatly from database to database, ... Why must everyone write in "Standard SQL"? ... can't help you because your code sucks. ...
    (comp.databases.ms-sqlserver)
  • Re: How do i write Set based queries and avoid a cursor?
    ... I am sure CELKO is an expert but 95% of us ... certain coding standards, which also may predate certain ANSI/ISO ... since this varies so greatly from database to database, ... Why must everyone write in "Standard SQL"? ...
    (comp.databases.ms-sqlserver)
  • Re: [NOW ANSWER Aarons QUESTION CELKO]
    ... No. Phil Shaw of IBM put their SQL spec into the old ANSI X3H2 as the ... the INCITS H2 Database Standards Committee(nee ANSI ...
    (microsoft.public.sqlserver.programming)