5 ms·
"The fastest way to access to a row in a database is by its primary key. If you have better ways to identify records, sequential IDs may make the most significa
by Dowwie 7y ago
"The fastest way to access to a row in a database is by its primary key. If you have better ways to identify records, sequential IDs may make the most significant column in tables a meaningless value. Please pick a globally unique natural primary key (e.g. a username) where possible."
Has anyone had a problem due to surrogate keys?
- ComodoHacker 7y agoI can agree with everything in the article except this one. >Has anyone had a problem due to surrogate keys? There's one problem with surrogate keys: they are not convenient to users (too long and not meaningful). There are two problems with natural primary keys, and you are guaranteed to hit one of them at some point. 1. It turns out your key isn't actually unique. To resolve the collision you have to replace natural key with a surrogate for one of conflicting entities. Which is not always possible without risk of another collision. 2. It turns out your key isn't persistent. You have to change it for some entities, but you can't because of so many FKs.
- Scarbutt 7y agoThere's one problem with surrogate keys: they are not convenient to users (too long and not meaningful). You shouldn't expose those keys to users though.
- brantam 7y agoThe article mentions auto-incrementing keys, not surrogates. Not the same thing at all. Not all incrementing keys are surrogates and not all surrogates are incrementing keys. Also, your problem number 1 is a problem whether the natural key in question is the "primary" one or not. Certainly if you choose the wrong natural key then you'll have to fix that - that's why you should take care to make a wise choice of natural key regardless of whether you are also using a surrogate.
- zip1234 7y agoNatural primary keys are trading one problem that can be solved with an index for another one that is not easily solved.