I'm taking a survey.



How do you feel about using AutoNumber feilds as primary keys?



Do you use them religiously (e.g., even if a table already contains

a "natural" primary key like a Social Security Number or in-house 

Client ID)?



Do you use them as the rule but make exceptions (e.g., natural primary

keys or in validation ("lookup") tables where it's helpful to

maintian sequential numbering?



Do you avoid them like the plague?



I'm just looking for a kind of "when to and when not to" rationale in the

interests of table structure consistency.



Thanks for your thoughts.

My practice is to use natural keys when possible--especially in Access,

since it makes cascading updates so easy to do.  I start looking at

Autonumbers when the only natural key is made up of three or more fields, or

when the volume of data is such that I'll need the table joins to be as

efficient as they possibly can be.  I do not rely on them to maintain

perfect sequential numbering, since deletes can pretty easily mess that up.



When I do use an autonumber, I'm sure to also define a unique index on the

natural key, so the autonumber uniqueness doesn't mask duplicate rows (if

you know what I'm trying to say).



NB: I'm the token Access programmer in an otherwise all-ORACLE shop.  My

collegues here use ORACLE's equivalent of the Autonumber ('sequences')

religiously, out of concern for rare-but-necessary natural key updates.  In

ORACLE, if you needed a PK update to cascade out to child tables you'd have

to write triggers on the affected tables--not an attractive prospect.  I

believe that's also true of SQL Server 7.0, tho I think SQL Server 2000 now

does cascading updates.



Cheers,



-Roy



-----Original Message-----

From: Bob Bedell [mailto:bdbedell@m...]

Sent: Monday, February 26, 2001 6:51 AM

To: Access

Subject: [access] AutoNumbers as primary keys.





I'm taking a survey.



How do you feel about using AutoNumber feilds as primary keys?



Do you use them religiously (e.g., even if a table already contains

a "natural" primary key like a Social Security Number or in-house 

Client ID)?



Do you use them as the rule but make exceptions (e.g., natural primary

keys or in validation ("lookup") tables where it's helpful to

maintian sequential numbering?



Do you avoid them like the plague?



I'm just looking for a kind of "when to and when not to" rationale in the

interests of table structure consistency.



Thanks for your thoughts.

What you describe as "natural" primary keys are actually known as semantic 

keys in database work. A semantic key is a value from the real world which 

appears to identfy a record in a unique way - a National Insurance number 

in an employee database, for instance. Everyone has an NI value, and no one 

can share it, right? Right? Reality always throws up exceptions. Say you 

employ a foreign worker on a temporary visa, for example - no NI No., 

suddenly no primary key.



Even in the best of worlds, you now find yourself _inventing_ imanginary-

real values for these records - which is only cool until someone forgets 

that it's an imaginary value and fills out an NI return for the guy. In the

worst of worlds, your DB screws up and starts applying the same NI value to

all the overseas workers, or you end up hiring someone who _actually_has_

the NI no. you just applied to Mr Temp... And then the boss goes and open a 

Paris office!



Semantic keys are a fundamentally bad idea in database design and should

never be used. The database is for _storing_ data. using that data to do 

the work of the database itself, is like using the books to hold the 

shelves up: looks like a bookshelf, but is actually as useful as a choloate

teapot. Always use an autonumber field as the primary key. You can

always hide it, if it is likely to confuse the users of the database.



Daniel Walker

Wrox Press



> I'm taking a survey.

> 

> How do you feel about using AutoNumber feilds as primary keys?

> 

> Do you use them religiously (e.g., even if a table already contains

> a "natural" primary key like a Social Security Number or in-house 

> Client ID)?

> 

> Do you use them as the rule but make exceptions (e.g., natural primary

> keys or in validation ("lookup") tables where it's helpful to

> maintian sequential numbering?

> 

> Do you avoid them like the plague?

> 

> I'm just looking for a kind of "when to and when not to" rationale in the

> interests of table structure consistency.

>