Hacker News new | past | comments | ask | show | jobs | submit login

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.

ON UPDATE CASCADE. Tis a pity it's not more widely implemented.

Which 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.

Fair enough. I'm more worried about database consistency -- it's just the way I roll, I guess.

I'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. =)

There 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.

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact