Re: Enforce primary key on every table during dev?

[Ron Johnson <ron.l.johnson@xxxxxxx>]

On 03/01/2018 12:32 PM, Daevor The Devoted wrote:

On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson <ron.l.johnson@xxxxxxx> wrote:

On 03/01/2018 11:47 AM, Daevor The Devoted wrote:

On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar <rakeshkumar464@xxxxxxx> 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.

Could you perhaps elaborate on how a surrogate key allows one to insert garbage into the table? I'm afraid I don't quite get what you're saying.

If your only unique index is a synthetic key, then you can insert the same "business data" multiple times with different synthetic keys.

--
Angular momentum makes the world go 'round.