Re: Use of numeric pseudo-key vs. text key

In article <3AC823E9.FE15CAC9_at_elbanet.co.at>, Heinz Huber <Heinz.Huber_at_elbanet.co.at> writes
>
>
>Srinivas Venigalla wrote:
>>
>> There is a plenty of discussion in this group on this topic. you may want to
>> search using a good search engine like google. But here is my two cents
>> worth:
>>
>> A primary key must be part of the data the table is representing. Good
>> examples of a Primary key are: Emp_ID, Product_ID, SS_NO, tel_no, etc. They
>> are naturally unique keys within the enterprise or the domain.
>[snip]
>
>Beware of the pitfalls of those "naturally unique" keys. Some of them
>tend to be not unique! E.g. the SS_NO you mentioned: The authorities
>succeed in assigning the SAME SS_NO to different individuals every now
>and then. And then you're really screwed if you've designed your
>database with the SS_NO as PK.

Also note that all of the examples quoted are surrogate keys. Emp_ID is used as a key because there's no natural key that uniquely identifies an individual person. Even phone numbers are just an arbitrarily assigned code.

They can be treated as natural keys because we trust the system that generates them to enforce their uniqueness. In the case of employee or product codes that's because we control the system. If you don't trust the system then you have three alternatives:

1. Use surrogate keys
2. Assume uniqueness but add some error-handling code
3. Assume uniqueness and hope you get another job before anyone finds the problem.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk