Re: OID's vs Relational Keys?

Certainly Microsoft SQL Server allows this but as for the rest I don't use, perhaps its another one of those things where the use of standard sql falls flat on its face.

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 ),     blah int
)

You are right about the clustering, but i got that bit wrong anyway; for the meta tables like 'sector' you would cluster on the 'id' column rather than the primary key, that is because throughout the application you are using the 'id' as reference rather than the natural primary key. Its a bit like how you would design a schema to support dimensional modelling and your fact table having all the references (star schema). For the 'fact' and i use that loosely you would cluster on something data orientated, in a trade sense - trade date.

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


"Jay Dee" <ais01479_at_aeneas.net> wrote in message 
news:KdIrf.222719$Hs.105720_at_tornado.ohiordc.rr.com...
> 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 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.

>>

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