Michael Valentine Jones (10/15/2010)
Tom.Thomson (10/15/2010)
Michael Valentine Jones (10/14/2010)
Always use Surrogate keys for the primary key. Add unique constraints on all natural keys (alternate keys).Any other approach will eventually get you in trouble.
Do you really believe that or are you just trolling?
Of course I believe what I say.
I'm somewhat surprised, because it seems a very extreme position. It makes exactly as much sense to me as saying "always use natural keys" (ie none at all).
I didn’t want to get into a big debate because it never changes anyone’s mind, but I just thought this thread needed to have a counterpoint to what seemed to be a “use natural keys” lovefest so that new people are aware that is not a universal sentiment, and that the always use surrogate keys position is a valid approach.
To briefly state my position:
I always use surrogate primary keys for the simple reason that I have never had reason to regret that, but have regretted the use of natural keys (usually be other people) on many, many occasions when those natural keys turned out to be not so immutable or to even be unique.
I will stick to my own position, which is to use a surrogate key when it it not sensible to use a natural key, use a natural key when it is not sensible to use a surrogate key, and where each will work use whichever will work better. I've been badly bitten by a design (not mine - I just had to clear up the mess when the system fell in a heap) that used surrogate keys for everything, and equally badly bitten by a design (again not mine, I just ended up doing cleanup) that used natural keys for everything (someone used an 800 byte collection of columns as the clustering key for one table, as well as as a forign key in several tables referncing that one). In my experience it has often been useful to use a natural key than not, but not anything like 100% of the time.
Tom