Re: Enforce primary key on every table during dev?

On 02/03/2018 01:10 , Daevor The Devoted wrote:
>
>
> On Fri, Mar 2, 2018 at 12:05 AM, Gavin Flower
> <GavinFlower(at)archidevsys(dot)co(dot)nz <mailto: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>
> <mailto: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.
Me too. Another +10.

---
El software de antivirus Avast ha analizado este correo electrónico en busca de virus.
https://www.avast.com/antivirus