View Single Post
  #18  
Old August 7th, 2007, 09:13 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default PK - To AutoNumber or Not To AutoNumber - That is the Question

On Aug 4, 12:12 am, "Jeff Boyce" wrote:
I tend to use Autonumber PKs for most of my tables, much to the dismay of
the "natural key" purists (heh, heh, heh!).


Dismay? No, I don't think that's it, though I may not be
representative because I'm more respectful of "alternative lifestyle
choices" (e.g. autonumber as surrogate) than the "purist" moniker
implies. My response would depend on whether you'd also put a unique
constraint on the natural key e.g. employee number in the OP's case.
If you'd seemingly given it no serious consideration I'd call you
unprofessional. If you'd considered it and still failed to constrain
the natural key my emotional repose would reflect the contempt you
would have yourself shown for your clients and end users. If you'd
taken steps to put unique constraints on both autonumber and natural
key but had chosen to give the PRIMARY KEY (SQL keywords in uppercase)
designation to the meaningless autonumber column, I'd be slightly
saddened, though remain unsurprised, that an Access MVP was not aware
of the Access/Jet engine's clustered-key compact method that was
introduced in version Jet 3.0 (http://support.microsoft.com/kb/
137039), especially with me banging on about it all this time

Jamie.

--