AutoNumber (Primary Key)

[Gregg Knapp]

I'm setting up an Access DB (v2003) for use as the backend of
a web application. As the db grows I see the potential of upsizing
the db to SQL Server.

So I'm wondering...
I've heard (and read) that it's not a good idea to use an AutoNumber
for the primary key of a table in Access. Is there valid reasoning
behind that school of thought? I'm curious because I haven't run into
any issues w/ them before, but am looking down the road to a time
when I'll need to take a SQL Server migration into consideration.
I'd much rather spend the extra time now than have to rework it
later - Assuming this is an issue.

Just a general question. Any input would be most welcome.
Thanks!

Gregg Knapp

 

[Armen Stein]

I'm setting up an Access DB (v2003) for use as the backend of
a web application. As the db grows I see the potential of upsizing
the db to SQL Server.

So I'm wondering...
I've heard (and read) that it's not a good idea to use an AutoNumber
for the primary key of a table in Access. Is there valid reasoning
behind that school of thought? I'm curious because I haven't run into
any issues w/ them before, but am looking down the road to a time
when I'll need to take a SQL Server migration into consideration.
I'd much rather spend the extra time now than have to rework it
later - Assuming this is an issue.

Just a general question. Any input would be most welcome.
Thanks!

Gregg Knapp

Hi Gregg,

Although some developers disagree, I think most feel that using
AutoNumber keys (Identity in SQL Server) for the primary keys in most
tables are the best way to go. In our shop, our standard is to use them
for almost all tables.

The main thing they provide is a truly unique value without user or
programming intervention, and they prevent problems with layers of
compound primary keys in related tables.

In the past, Access had some problems with duplicate AutoNumber values,
but this issue has been resolved.

Hope this helps,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

 

[Allen Browne]

Definately agree. Use the AutoNumber.

If you ever upsize, it will move to the IDENTITY type fine.

Just make sure you have the JET 4 Service Pack 8 installed from the
Downloads section at support.microsoft.com and the AutoNumber will be fine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Gregg Knapp]

Great!
Thanks, guys! I feel much better now...

Gregg

Definately agree. Use the AutoNumber.

If you ever upsize, it will move to the IDENTITY type fine.

Just make sure you have the JET 4 Service Pack 8 installed from the
Downloads section at support.microsoft.com and the AutoNumber will be fine.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Craig Alexander Morrison]

Gregg, sorry to come to this late.

If you are to use the AutoNumber as a surrogate key you should really also
define the actual (natural) primary key (should it exist) within the table
as a unique, no nulls index. If you do not do this you are able to create
duplicate records with the only difference being the AutoNumber.

No doubt you had already considered this. It is worth pointing out though.

Craig Alexander Morrison

 

[Lynn Trapp]

Craig,
I definitely agree. Of course, however, some tables make finding a natural
key pretty difficult -- thus the invention of the concept of a surrogate
key. In those cases, rather than using a unique index on the natural key, I
write code, in the BeforeUpdate event of the form, to check for duplicates
and give the user the choice of allowing it to be inserted into the table or
not.

 

[Armen Stein]

Craig,
I definitely agree. Of course, however, some tables make finding a natural
key pretty difficult -- thus the invention of the concept of a surrogate
key. In those cases, rather than using a unique index on the natural key, I
write code, in the BeforeUpdate event of the form, to check for duplicates
and give the user the choice of allowing it to be inserted into the table or
not.

We tend NOT to enforce uniqueness on other natural keys, unless it would
be a true error if duplicates occurred. Sometimes you can limit a
system too much (excessive input masks are another example of this).
Instead, we try to let the user make the decisions with their data, and
give them a warning (as Lynn suggests) and/or a "de-duplicator" utility
to merge records later.

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com

 

[Craig Alexander Morrison]

Armen

I maintain that if you have a natural key (and I do accept that there are
quite a few examples where you do not) you should always enforce its
uniqueness.

If you use a surrogate (even AutoNumber) because you do not have a natural
key then I agree with your proposition of allowing the user to decide at the
time of entry and/or to have some housekeeping function to review possible
duplicates.

If you use a surrogate because the Natural Key is volatile or too
complex then I maintain its uniqueness should be enforced.

The users define these things when the database is designed, not when
someone is using the system. The users that define the system will have the
seniority to establish the business rules, the everyday users may not have
the authority to violate the business rules. I accept that there are a great
many systems where the user that defines the system is also the everyday
user, I still think it is good to design in the business rules as much as
you can.

I am not sure what you mean by a true error, by definition if you have a
natural key (primary key or not) it would be a true relational error to
record a duplicate.