From: | Daevor The Devoted <dollien(at)gmail(dot)com> |
---|---|
To: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Enforce primary key on every table during dev? |
Date: | 2018-03-01 20:09:30 |
Message-ID: | CAAZnbVowhuw=LDGmq1fRYjUWTVwbuyBMaYf+qtFEY9PtOh49gQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Mar 1, 2018 at 9:00 PM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:
> On 03/01/2018 12:32 PM, Daevor The Devoted wrote:
>
>
>
> On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:
>
>>
>> On 03/01/2018 11:47 AM, Daevor The Devoted wrote:
>>
>>
>> On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar <rakeshkumar464(at)aol(dot)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.
>>
>
> 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.
>
That might be where we're talking past each other: I do not advocate for
the arbitrary primary key being the only unique index. Absolutely not.
Whatever the business rules say is unique must also have unique indexes. If
it's a business constraint on the data, it must be enforced in the DB (at
least, that's how I try to do things).
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2018-03-01 20:12:21 | Re: Enforce primary key on every table during dev? |
Previous Message | marcelo | 2018-03-01 20:08:57 | Re: Enforce primary key on every table during dev? |