On 03/06/2014 03:52 PM, Merlin Moncure wrote:
> On Tue, Mar 4, 2014 at 3:08 PM, Rob Sargent <robjsargent(at)gmail(dot)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.