Re: PostgreSQL Developer Best Practices

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Melvin Davidson
Sent: Tuesday, August 25, 2015 8:18 PM
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Cc: Jerry Sievers <gsievers19(at)comcast(dot)net>; John R Pierce <pierce(at)hogranch(dot)com>; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] PostgreSQL Developer Best Practices

….
Before ANYONE continues to insist that a serial id column is good, consider the case where the number of tuples will exceed a bigint.
Don't say it cannot happen, because it can.
……………………
Melvin Davidson

Now, it’s easy to overcome this limitation.
You just make concatenated PK (id1, id2) with both columns of BIGINT type.

In general, I see the main advantage of artificial PK in NO NEED to change multiple child tables, when NATURAL key changes in the parent table. And I never saw a system where NATURAL key wouldn’t need to be changed eventually.
So, my conclusion: use artificial PK (for db convenience) and unique NATURAL key (for GUI representation).

Regards,
Igor Neyman