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 is a fair point and certainly I wouldn't want one to assume from my previous post that I'd always insist on surrogate keys. I'm dogmatic about very little (other than that the title Technical Architect is meaningless drivel). Database Management is surely a pragmatic discipline as well as a theoretical one. I do have a *preference* for surrogate keys though as well as a preference to hide them from users.

Of course if I had a 30 sq m rose bed to deal with it would take me all year to look after it and the point would be moot.

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

*If* its needed its needed.

> 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).

No do go on (or at least point me at TFM) about the issues with reverse key indexes.

--
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
******************************************