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

I have two problems with surrogate keys.

First, they ensure uniqueness of an entry which oughtn't to be permitted. As an example, a landscaping application I once had a hand in permitted users to say 'on site X, there is a Rose bed (BR1), plot 1, and it's 30 sq.m'. There is also a BR1 plot 2 of 25 sq.m. It was important not to add another BR1 plot 1 or plot 2, since you'd end up pruning the wrong rose bed. But if all landscaping features were being added with surrogate primary keys, there would have been nothing to stop the insertion of such a duplicate record, since the surrogate key would have been unique.

I suppose I could have slapped a unique constraint on the site, feature and plot number ... but that's now two indexes, not one.

Second, indexes on surrogate keys (ie, monotonically incrementing sequence numbers) are a royal pain in the contention stakes, and I might not have wanted to pay for the partitioning option so that I could have partition the table to avoid the contention (and the less said about reverse keys the better).

Just my square metre's worth.
Regards
HJR "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3dd91bd1$0$8514$cc9e4d1f_at_news.dial.pipex.com...
> "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.
>
>
> --
> 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
> ******************************************
>
>
Received on Mon Nov 18 2002 - 13:05:00 CST