On Thu, Mar 1, 2018 at 9:00 PM, Ron Johnson <ron.l.john...@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.john...@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 <rakeshkumar...@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).
