Re: r there downsides to explicitly naming a pk column xxxx_pk - Mailing list pgsql-novice

All,

Please do not reply to this- I've already reached out to Sharon
directly.

Thanks!

Stephen

* Sharon Giannatto (Sharon.Giannatto@jax.org) wrote:
> Can someone tell me how to unsubscribe? There is no unsubscribe link. I just started getting these messages out of
theblue in the last couple of weeks – though I recall belonging to a forum years ago. 
>
> --
> "Don't kill -9 the messenger ..."
>
> Sharon Cousins Giannatto
> Scientific Software Engineer
> The Jackson Laboratory
> Mouse Genome Informatics
> 600 Main Street
> Bar Harbor, ME 04609 USA
> (207) 288-6292 (V)
> ******************************
> sharon.giannatto@jax.org<mailto:sharon.giannatto@jax.org>
> http://www.informatics.jax.org<http://www.informatics.jax.org/>
> ******************************
>
>
>
> From: Greg Robson [mailto:gregrobson@gmail.com]
> Sent: Thursday, December 14, 2017 3:59 PM
> To: Gavin Flower
> Cc: David G. Johnston; john snow; pgsql-novice@postgresql.org
> Subject: Re: r there downsides to explicitly naming a pk column xxxx_pk
>
>
> My personal preference is {table_name}_key for primary keys.
>
> Reason 1
> Should you use a NATURAL JOIN the common column will only appear once in the set of returned columns.
> http://www.postgresqltutorial.com/postgresql-natural-join/
>
> Reason 2
> It removes ambiguity.
>
> "foo.id<http://foo.id>" can easily be mis-typed as "bar.id<http://bar.id>" and a query might still run, with possible
nastyside effects. That can catch you out if it has been a long day! 
>
> If you type "bar.foo_id" instead of "foo.foo_id" the query will fail as table and column do not match.
>
> Reason 3 (a)
> It's unlikely that the primary key on a table might change, but I don't like to tie function (data type, index
status)to the name of a column. e.g. I would never use "unique_email_address" or "text_total". 
>
> Reason 3 (b)
> A primary key might consist of one column initially, but then might expand to become a composite key, at that point
youhave to start renaming columns. 
>
>
>
>
> On 14 December 2017 at 20:41, Gavin Flower <GavinFlower@archidevsys.co.nz<mailto:GavinFlower@archidevsys.co.nz>>
wrote:
> On 12/15/2017 09:22 AM, David G. Johnston wrote:
> On Thu, Dec 14, 2017 at 1:14 PM, john snow <ofbizfanster@gmail.com<mailto:ofbizfanster@gmail.com>
<mailto:ofbizfanster@gmail.com<mailto:ofbizfanster@gmail.com>>>wrote:
>
>     instead of the more conventional xxxx_id or just id?
>
>     sorry if this may be a foolish question to some, but i'm trying to
>     think thru
>     a junior colleagues's proposal. the discussion occurred while we were
>     discussing naming our foreign key constraints using the convention
>     "childtable_parenttable_colname_fk".
>
> ​Are you talking about the constraint name or the name of the column holding the data?​
>
> ​Identifiers in PostgreSQL can only be 64 characters (bytes?) long.
>
> If it is the column name I wouldn't get too crazy or people writing out SQL joins manually will be asking you to pay
theirmedical bills... 
>
> I generally avoid naming any column "id" - tables get short code aliases and those prefix the "id".  I then name the
columnin the FK the exact same name.  I rely on system defaults for choosing the names of the corresponding constraints
andindexes. 
>
> David J.
> I use 'id' for the primary key of the current table, and 'xxx-id' for a foreign key.
>
> So it is easy to identify the primary key, and to spot the foreign keys.
>
> Since we know the current table name, it is redundant to name the table's primary key with the its table name.
>
>
> Cheers,
> Gavin
>
>
> ---
>
> The information in this email, including attachments, may be confidential and is intended solely for the
addressee(s).If you believe you received this email by mistake, please notify the sender by return email as soon as
possible.