Re: Enforce primary key on every table during dev?

On 01/03/2018 19:05 , Gavin Flower 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
>
>
>
>
+5. I fully agree.

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