Re: PostgreSQL Developer Best Practices

On 25/08/15 01:15, Ray Cote wrote:
> On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert
> <Karsten(dot)Hilbert(at)gmx(dot)net <mailto:Karsten(dot)Hilbert(at)gmx(dot)net>> wrote:
>
[...]
>
> 9. Do NOT arbitrarily assign an "id" column to a table as a
> primary key when other columns
> are perfectly suited as a unique primary key.
>
> ...
>
> Good example:
> CREATE TABLE accounts
> ( accout_id bigint NOT NULL ,
>
>
> I would not consider the general use of natural primary keys to be
> best practice.
[...]

Neither would I.

The database has primary keys that are often foreign keys for other
tables. So if the primary key is a natural key, then if the external
world redefines the nature of the natural key, for example changing its
type or format, then this would have unnecessary invasive changes to
multiple tables within the database. Also you are at the mercy of
external control of what constitutes uniqueness, for example the
American Social Security Number is not unique!

Also the best practice is to make the primary key name 'id' as you do
know the table it is in, so prepending the table name is redundant - so
you can clearly identify foreign keys because the suffix '_id 'is
prepended by the table name of the referenced table. Hence 'id' is a
primary key, and account_id is a foreign key pointing into the account
table.

I have had to deal with databases were a child table's primary key is
the parent table's primary key with extra characters appended, so you
can have a child table's primary key exceeding 45 characters. The child
table only need to know the primary key of it direct parent, so using
int, or bigint, would be a far better solution!

Having said the above, there may well be valid reasons to use a natural
key for the primary key - so it should NOT be an absolute rule to
disallow it.

Cheers,
Gavin