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

In the system we built that used a sequential 9-digit ID number as the primary key we tried to ease contention issures by actually incrementing only the low-order 8 numbers and then using a MOD(10) algorithm to generate a unique first digit prefix. This way the generated first digit functioned as a check digit for the entire 9-digit number, and we scattered the new records across 10 hot spots instead of just one. Of course, that was many years ago and this may not be as helpful these days.

On Tue, 19 Nov 2002 06:05:00 +1100, "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote:

>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 - 16:01:49 CST