Re: OID's vs Relational Keys?

>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 sector.id 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 http://www.dbdebunk.com site.

-- 
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"-CELKO-" <jcelko212_at_earthlink.net> wrote in message 
news:1135276624.698019.131500_at_g47g2000cwa.googlegroups.com...


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

> classic hardware  pointers you are talking about, row_id within a table

> structure, hash table values, etc.  Anything that:

>

> 1) Is not an attribute in the reality of the data model.  in the

> Relational keys are a set of attributes by definition.  Nothing to do

> with physical storage.

>

> 2) They are generated inside the machine and depend on the internal

> state of the machine at creation time.  Nothing to do with the data

> model or the real world.

>

> And I do believe in duplicate values, but not duplicate data elements.

> So did Dr. Codd when he introduced a "Degree of Duplication" operation

> in his second version of  RM. Codd also defined a surrogate as being

> hidden from users, so things like auto-numbering and IDENTITY do not

> qualify in most products.

>