It is friday and I am bored so I thought I would present another question.
Anymore, I primarily design Websites. For the past couple of years it has been the trend to use an incrementing integer as the primary key and then just put constraints on the fields that should be unique. I have also noticed a little more frequently people are starting to go to GUIDs. However, I was always under the impression that for webfarms this was not the best strategy.
I was wondering what peoples thoughts were on what type of key to use, traditionally tought DB design would never go for integers as the keys as they truly do not make the row unique and are not needed to identify anything. I just thought it would be an interesting discussion!
If there is a "real" unique identifier that NEVER changes, and is not made up of too many columns, then generally speaking it's good to make it the primary key, rather than introduce a surrogate unnecessarily.
The trouble is, "real" UIDs often can change in practise, due to mistyping if nothing else. For example, SSN is a real UID for a person, but just occasionally someone will set up a person record with an incorrect SSN and subsequently need to change it. Surrogate keys are a way of avoiding this problem. Having said that, the SSN should still be constrained to be unique. If the ONLY uniqueness constraint on table is the surrogate key, then there's nothing to stop you setting up duplicate rows that differ only by the surrogate key.
A downside of surrogates is that they are meaningless, so you have to make more joins in your queries to find out what they represent. This is more of a pain when doing ad hoc SQL queries than in building applications - and judicious use of views can overcome that.
Whether a surrogate is globally unique or just unique within the table seems irrelevant to me, though I know the OO brigade like GUIDs - presumably because "everything is an object". Don't get me started...