>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.
www.avast.com
