Re: Enforce primary key on every table during dev?

On 03/01/2018 02:09 PM, Daevor The Devoted wrote:
>
>
> On Thu, Mar 1, 2018 at 9:00 PM, Ron Johnson <ron.l.johnson@cox.net 
> <mailto:ron.l.johnson@cox.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.l.johnson@cox.net
>>     <mailto:ron.l.johnson@cox.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@aol.com <mailto: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.
>>
>>
>>     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).

Why have the overhead of a second unique index?  If it's "ease of joins", 
then I agree with Francisco Olarte and use the business logic keys in your 
joins even though it's a bit of extra work.

-- 
Angular momentum makes the world go 'round.