Home -> Usenet -> comp.databases.theory -> Re: OID's vs Relational Keys?

Re: OID's vs Relational Keys?

From: Tony Rogerson <tonyrogerson_at_torver.net>
Date: Sun, 25 Dec 2005 12:54:42 -0000
Message-ID: <dom4h7$8kq$1$8300dec7_at_news.demon.co.uk> 






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

> 


Received on Sun Dec 25 2005 - 13:54:42 CET












Original text of this message