On Tue, 2015-08-25 at 15:41 +0000, Neil Tiffin <neilt@neiltiffin.com>
wrote:
> I really like the standardization that PostgreSQL uses in auto
> generating default names. The rule I use is to always use the auto
> generated names unless the object is referenced routinely in code. In
> most cases developers don’t care about index, unique, foreign key, or
> primary key names (from a coding standpoint) so why should they be
> creating the names. Since the postgresql standard uses auto generated
> names with ‘_pkey’ for PRIMARY KEY ‘_fkey’ for FOREIGN KEY, and
> ‘_key’ for UNIQUE, why not use the same rules for consistency? So I
> disagree with 6 and would extend 10 to include these other names if
> they are manually generated.
I prefer to take control of names in order to be certain that on
multiple database instances, the names will *always* be the same. This
allows schema-diff tools (like my own skit) to provide more useful
results. Although, as you point out, Postgres does a pretty good job of
naming things, there are (or at least have been in the past) cases where
names have not been predictable.
Furthermore, a policy of explicit naming seems to me a relatively light
burden on a developer or DBA, and one that may even lead to more thought
being applied during database object design. If the developer has to
think of a name, they may be more inclined to think more deeply about
the purpose of that object.
For the record, I favour using a double underscore to separate the
table_name part of constraints, etc from any other parts of the name.
So:
account__name_idx would be an index on the name field of the
accounts table;
account_name__pk would be a primary key on the account_names
table.
It's a personal preference and works for me, your mileage may vary.
__
Marc