> On Aug 22, 2015, at 10:15 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
> 6. Although it is legal to use the form "column TYPE PRIMARY KEY," It is best to specify as a CONSTRAINT,
> that way YOU get to choose the name, otherwise postgres assigns a default name which may not be to your liking.
> EG: , CONSTRAINT accounts_pk PRIMARY KEY (account_id)
> 10. Standardize Index names with the form table_name + col(s) + “idx”
> EG: For accounts table:
> accounts_name_idx
> accounts_city_state_idx
>
I really like the standardization that PostgreSQL uses in auto generating default names. The rule I use is to always
usethe auto generated names unless the object is referenced routinely in code. In most cases developers don’t care
aboutindex, 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
theseother names if they are manually generated.
interestingly enough, when I searched 9.5 docs I could not find a description of these postgreSQL naming convention.
Probablybecause the developers consider it an internal detail that could change which is fine, since the names usually
don’tmatter, until they do.
I would say use “column TYPE PRIMARY KEY”, “column TYPE UNIQUE”, and ‘column TYPE REFERENCES …’ every place you can and
onlycreate manual names when absolutely necessary. When you do create manual names follow the standard PostgreSQL
convention.
Now I have worked on mostly smaller installations so maybe someone should chime in if this is a bad best practice.
Neil