On Fri, Mar 2, 2018 at 12:05 AM, Gavin Flower <gavinflo...@archidevsys.co.nz > wrote:
> On 02/03/18 06:47, Daevor The Devoted wrote: > >> >> On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar <rakeshkumar...@aol.com >> <mailto: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! So using a primary key whose sole >> purpose is to be a primary key makes perfect sense to me. >> > > I once worked in a data base that had primary keys of at least 4 columns, > all character fields, Primary Key could easily exceed 45 characters. > Parent child structure was at least 4 deep. > > A child table only needs to know its parent, so there is no logical need > to include its parent and higher tables primary keys, and then have to add > a field to make the composite primary key unique! So if every table has > int (or long) primary keys, then a child only need a single field to > reference its parent. > > Some apparently safe Natural Keys might change unexpectedly. A few years > aback there was a long thread on Natural versus Surrogate keys - plenty of > examples were using Natural Keys can give grief when they had to be > changed! I think it best to isolate a database from external changes as > much as is practicable. > > Surrogate keys also simply coding, be it in SQL or Java, or whatever > language is flavour of the month. Also it makes setting up testdata and > debugging easier. > > I almost invariably define a Surrogate key when I design tables. > > > Cheers, > Gavin > > > Thank you! I think you have expressed far more clearly what I have been trying to say. +10 to you.
