Daniel...

Please give me an instance where the use of surrogate PK's hurts you...

There is a big difference between considering alternatives and choosing to always use surrogate keys. I for one always have an eye out for FEASIBLE alternatives. Choosing to not use surrogate PK's is an immutable rule for me. It is like the naming conventions I use or the way I handle RI, or the way I handle parent/child relationships. I have a few rules that I ALWAYS use.

Of course, if I encounter a situation where an immutable rule does not apply, I guess it ceases to be an immutable rule. And, this has happened before. That cannot be said for surrogate PK's.

One thing to keep in mind in answering the question above. You can't answer it from the perspective of the benefit that a natural PK gives you. You have to answer it from the perspective of how the use of surrogates directly hurts your app or design...


< JVP >



>Jim:
>
>> While I agree with your proposition that any absolute is a problem in
>> the simple fact that it is an absolute, I would still propose that use
>> of surrogate primary keys in all cases where there is no overwhelming
>> evidence that to do so would cause problems is a very good "best practise".
>> Remember in this context that "best practise" is not an absolute but a
>> general approach to be followed unless overwhelming evidence indicates
>> otherwise.
>
>I agree that using surrogate keys is more often than not the best solution
>and I am glad to see that you for one will consider using natural keys in
>some specxial cases.
>
>My problem, and it is not restricted to this thread, is that too many people
>are saying things like Thou shall always use surrogate keys without
>actually considering the problem at hand. It is likely that some developers
>got burned a few times using natural keys and stoppped using them because of
>those scars. If I had stopped considering alternatives because of bad past
>experience, I wouldn't be able to solve half the problems that I solving
>today...
>
>Daniel