Home > mailing lists

Thread: r there downsides to explicitly naming a pk column xxxx_pk

r there downsides to explicitly naming a pk column xxxx_pk

From

john snow

Date:

15 December 2017, 02:14:50

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

thanks for any guidance!

Re: r there downsides to explicitly naming a pk column xxxx_pk

From

"David G. Johnston"

Date:

15 December 2017, 02:22:41

On Thu, Dec 14, 2017 at 1:14 PM, john snow <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 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.

Re: r there downsides to explicitly naming a pk column xxxx_pk

From

john snow

Date:

15 December 2017, 02:40:50

it 4am here right now sorry :-)

i'm talking about the name of the column holding the data. I mentioned the fk constraint naming part only because we've decided on suffixing the constraint name with an "fk", and i guess that led to his suggestion of suffixing a table's pk column with "pk". for example: we have a table named "units" (for units of measurement); its pk currently is named unit_id. he wanted to name it "unit_pk".

On Fri, Dec 15, 2017 at 4:22 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Thu, Dec 14, 2017 at 1:14 PM, john snow <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 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.

Re: r there downsides to explicitly naming a pk column xxxx_pk

From

Gavin Flower

Date:

15 December 2017, 02:41:08

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

Re: r there downsides to explicitly naming a pk column xxxx_pk

From

"David G. Johnston"

Date:

15 December 2017, 02:48:56

On Thu, Dec 14, 2017 at 1:41 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:

Since we know the current table name, it is redundant to name the table's primary key with the its table name.

Redundant but useful - I am a huge proponent of USING clauses in joins:

pk_tbl JOIN fk_tbl USING (pk_tbl_id)

As for "finding the PK" - its almost always the first column in the table, ends in "id", and has a reasonable prefix.

Being able to "grep pk_tbl_id" is also nice in many cases. Grepping "id" provides no useful value - though if one always uses table prefixes then grepping "pk_tbl.id" would mitigate that particular problem.

David J.

Re: r there downsides to explicitly naming a pk column xxxx_pk

From

Greg Robson

Date:

15 December 2017, 02:58:48

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

RE: r there downsides to explicitly naming a pk column xxxx_pk

From

Sharon Giannatto

Date:

15 December 2017, 03:01:11

Can someone tell me how to unsubscribe? There is no unsubscribe link. I just started getting these messages out of the blue 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

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" 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)

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

---

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.

Re: r there downsides to explicitly naming a pk column xxxx_pk

From

Stephen Frost

Date:

15 December 2017, 03:09:51

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.

Attachment

signature.asc