On 03/06/2014 03:52 PM, Merlin Moncure wrote:
On Tue, Mar 4, 2014 at 3:08 PM, Rob Sargent <robjsargent@gmail.com> wrote:
Do you make a distinction between a key and an index? I'm not picking up on
design-by-natural-key and what that entails. Especially the notion that the
natural key of a given item might be mutable. What stops it from colliding
with the next item? (I have not had the pleasure of working in a domain
where natural keys are obvious if they existed at all. "What's in a name",
after all. )
If your keys are mutable then you definitely have to take that into
consideration for key style choice...but not for your stated concern.Even though you can cascade (via RI) updated keys to various tables
performance can certainly suffer vs a surrogate. This is the main
reason not to use natural keys: slow, perhaps even pathologically slow
update performance on the key.
However, collisions are a reason *to* use natural keys. If you can'd
handle them with your proposed key then either:
a) you've misidentified the key
or
b) you'er allowing duplicate unique entries in the system and when you
should not be
Even when using surrogates, it's still a good practice to identify
what makes a record unique wherever possible and place unique
constraints on those fields.
merlin
Yes. And in my mind therein resides the semantic difference between a primary key and a unique index.
Thanks.