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@archidevsys.co.nz <mailto:GavinFlower@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
>         <rakeshkumar464@aol.com <mailto:rakeshkumar464@aol.com>
>         <mailto: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! 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