Re: Enforce primary key on every table during dev? - Mailing list pgsql-general

On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar <rakeshkumar464@aol.com> wrote:

>Adding a surrogate key to such a table just adds overhead, although that could be useful
>in case specific rows need updating or deleting without also modifying the other rows with
>that same data - normally, only insertions and selections happen on such tables though,
>and updates or deletes are absolutely forbidden - corrections happen by inserting rows with
>an opposite transaction.

I routinely add surrogate keys like serial col to a table already having a nice candidate keys
to make it easy to join tables.  SQL starts looking ungainly when you have a 3 col primary
key and need to join it with child tables.

I was always of the opinion that a mandatory surrogate key (as you describe) is good practice.

Sure there may be a unique key according to business logic (which may be consist of those "ungainly" multiple columns), but guess what, business logic changes, and then you're screwed!

And so you drop the existing index and build a new one.  I've done it before, and I'll do it again.

So using a primary key whose sole purpose is to be a primary key makes perfect sense to me.

I can't stand synthetic keys.  By their very nature, they're so purposelessly arbitrary, and allow you to insert garbage into the table.