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" can easily be mis-typed as "bar.id" and a query might still run,
with possible nasty side 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 you have to start renaming columns.

On 14 December 2017 at 20:41, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)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(at)gmail(dot)com
>> <mailto:ofbizfanster(at)gmail(dot)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 their medical bills...
>>
>> I generally avoid naming any column "id" - tables get short code aliases
>> and those prefix the "id". I then name the column in the FK the exact same
>> name. I rely on system defaults for choosing the names of the
>> corresponding constraints and indexes.
>>
>> 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
>
>
>