|
On 03/06/2014 03:52 PM, Merlin Moncure
wrote:
Yes. And in my mind therein resides the semantic difference between a primary key and a unique index.On Tue, Mar 4, 2014 at 3:08 PM, Rob Sargent <robjsargent@xxxxxxxxx> 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 Thanks. |
