Re: Enforce primary key on every table during dev?

On Fri, Mar 2, 2018 at 12:05 AM, Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz
> wrote:

> On 02/03/18 06:47, Daevor The Devoted wrote:
>
>>
>> On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar <rakeshkumar464(at)aol(dot)com
>> <mailto: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! 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.