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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<2JaC9.79258$g9.223489_at_newsfeeds.bigpond.com>...
> 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.

This first problem is pretty fundamental. I agree that in most cases so-called natural primary keys are not up to the task, because they are, most probably, updatable, and, almost for sure, not unique. However, any additional unique key on the table is going straight against 3rd normal form, which explicitly demands that the only dependency in the table is based on primary key only, i.e. forbids transitive dependencies. Now, with 2 unique keys on the table... You can make natural key non-unique, of course. But business rules tell you that it is unique. So transitive dependency is there anyway. I duck under cover, of course. I expect hundreds of people (OK, not hundreds) start shouting at me that practical means overweght theory, that no production DB ever was 3rd form compiant etc. All true. And my experience tells me that the closer to 3rd form you get, the simpler DB and application code you can have.

>
> 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 Tue Nov 19 2002 - 10:25:25 CST