Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

I see your point about natural keys tending to be updatable and non-unique over time. These qualities certainly render the natural columns un-usuable as stable, unique key fields.

You have led me to think more about just what my experiences here have been in building transaction-based systems.

What has been inportant here is not that key fields be fields otherwise meaningful in some way, but that they be readilly available and useable by the end users. We commonly use a sequentially-assigned ID number as the primary key to many of our tables. This number has no intrinsic meaning, aside from indicating the general "age" of an entry, but it is a widely printed and used identification number for the individuals in our systems. Thus it is easily used by end users in their tasks of accessing and updating the data.

By the way, systems here used to use social security number as the primary key. This ended when laws were instituted that no longer allowed the collection of the SSN. Other than that, it worked OK I guess.

Thank you for helping me clarify my thinking.  

On Mon, 18 Nov 2002 16:56:49 -0000, "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote:

>"Jim" <no-spam_at_no-spam.org> wrote in message
>news:lh1itu0ibhtnjt28jal4ne6ou7e6j1n267_at_4ax.com...
>> I confess I don't understand the preference for the use of a surrogate
>> key instead of a natural key except in special cases.
>>
>> I can see that a surrogate, "artificial" key would have some value IF
>> all "natural" columns in a table must be updatable, IF no 'natural'
>> column, or reasonable combination of 'natural' columns, would be
>> unique, and IF all updating of the table would be perfomed by
>> application programs able to deal with the essentially meaningless
>> surrogate key.
>>
>> On the other hand, use of a 'natural' key composed of column(s)
>> understood and meaningful to the user would, it seems to me, yield a
>> more straightforward, easier for the user to understand design.
>>
>> Am I missing something (probably)?
>
>Natural keys tend, in practice, to be either
>
>1. Updateable.
>2. Non-unique when business rules change or in the event of design error.
>
>In addition I'm tempted to ask why should the user know about a key at all.
>Most answers to this question assume a knowledgable user familiar with
>database concepts (for example a report developer) who is quite capable of
>using surrogate keys.
Received on Mon Nov 18 2002 - 11:15:07 CST