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

<ctcgag_at_hotmail.com> wrote in message
news:20021125194033.911$iI_at_newsreader.com...
> Galen Boyer <galenboyer_at_hotpop.com> wrote:
> > On 25 Nov 2002, ctcgag_at_hotmail.com wrote:
> > > "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote:
> > >> > As a side note, SSN's are intended to be unique but are not
> > >> > for a variety of reasons.
> > >>
> > >> A marvellous example of the problems of 'natural keys'.
> > >
> > > How so?
> >
> > Did you read the thread?

>
> Yes. It didn't answer my question.

My point was that one of the drawbacks of so called 'natural keys' is that they are attributes of the data which are considered by the application designer/stated business rules to be unique and non-null, there is therefore no need to assign an arbitrary key which is constrained to be unique and non-null because the data already has that attribute in a 'natural key'.

The problem is that the assumptions often turn out to be incorrect. It appears that US SSN's can have duplicated values. Equally I couldn't appear in such a database (not having an SSN). Pick an SSN as a natural key and you will eventually get duplicates or null values.

I do take the points about duplication of indices, my general point is that an artificial key actually does constrain the data whereas it is only *believed* that a natural key does. This belief may in practice be correct or incorrect. Of course if you then constrain the natural key to be unique and not null.....

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************