4 ms·
Not being able to find a natural key isn't why you use a surrogate. You use a surrogate because natural keys are not static: they can change. Surrogate allows
by jasonlotito 16y ago
Not being able to find a natural key isn't why you use a surrogate. You use a surrogate because natural keys are not static: they can change. Surrogate allows you to have a key that is unrelated to the data, data that will change. Basing keys on changing data is risky.
I've experienced this myself, and while natural keys do work in theory, in practice, they are prone to failure.
- jfb 16y agoON UPDATE CASCADE. Tis a pity it's not more widely implemented.
- jasonlotito 16y agoWhich does nothing for anything utilizing the resource itself outside the database. I'm not referring at all to keeping the database consistent. Having something you can always refer to to grab the same data is invaluable.
- jfb 16y agoFair enough. I'm more worried about database consistency -- it's just the way I roll, I guess.
- jasonlotito 16y agoI'm all for database consistency. It's just not only database consistency that I'm worried about. I've just been burned by keys that were also data before. Having keys that aren't related at all to the data just hurts a few sensibilities and a few individuals sense of calm. =)
- Devilboy 16y agoThere are limits to even this. For example, you may have a table referencing itself (e.g. for tree structures) and on most systems you can't cascade updates. Also if your key is used in lots of other tables the cascade will become expensive.