Re: OID's vs Relational Keys?

From: Jay Dee <>
Date: Mon, 26 Dec 2005 02:05:30 GMT
Message-ID: <KdIrf.222719$>

Tony Rogerson wrote:

>>I have invented the term "physical locator" to cover things like the

> No you didn't, its been used for a long time by many people and you
> certainly did not originate it.
> You are getting confused as to the purpose and usefulness of a surrogate
> key, we use auto-numbering schemes like the IDENTITY 'property' to easily
> create a surrogate key - the natural 'primary key' is just 'meta' data
> within the table, it should not be duplicated around the database schema
> because if it should change you run into all sorts of serious situations
> within the database and also within the application, here are some
> issues....
> On a change to the primary key...
> 1) Serious locking throughout the schema which will most likely cause
> deadlocks and severe locking contention
> 2) The row may move if clustered on that column, this will cause even
> more locking and contention - can you image the size of the transaction!
> 3) The application will lose contact with the row because the key being
> used in the application has been changed, worse still, if the key is changed
> and somehow another row gets the same key then the application will update
> the wrong row!
> Solution to this problem: assign and use a surrogate key on the base table
> and any reference tables.
> This can easily be done...
> create table sector (
> id int not null identity constraint sk_sector unique nonclustered,
> sector_code char(10) not null constraint pk_sector primary key
> clustered
> )
> create table trade (
> sector_id int not null references sector( id ),
> ....
> ....
> If the sector_code should ever change you need only tackle one table, the
> rest remains as is; the application will use internally in drop
> downs and for reference back to the database; the suer will never see this
> 'id' instead the meta data of the primary key will be displayed.
> So, this is still true to Codd, the user never sees the surrogate key, but
> you also get round the problems with duplicating the natural key everywhere.
> I think it interesting that Chriss Date and Fabian Pascal completely
> disagree with your opinion on this and other things including the nested
> sets on there site.
I don't see how use of a surrogate key avoids the situation in which "The row may move if clustered on that column." Won't that still be true "If the sector_code should ever change?"

The SQL standard allows foreign key references to columns which have not been declared as primary keys, but some significant SQL DB products don't. Are you sure you've got the "unique" and "primary key" constraints where you want them? Received on Mon Dec 26 2005 - 03:05:30 CET

Original text of this message