If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
AutoNumber (Primary Key)
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 |
#3
|
|||
|
|||
AutoNumber (Primary Key)
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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Armen Stein" wrote in message ... In article , says... 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/AS...jstreettech-20 J Street Technology, Inc. Armen _@_ JStreetTech _._ com |
#4
|
|||
|
|||
AutoNumber (Primary Key)
Great!
Thanks, guys! I feel much better now... Gregg "Allen Browne" wrote in message ... 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. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Armen Stein" wrote in message ... In article , says... 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/AS...jstreettech-20 J Street Technology, Inc. Armen _@_ JStreetTech _._ com |
#5
|
|||
|
|||
AutoNumber (Primary Key)
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 "Gregg Knapp" wrote in message ... Great! Thanks, guys! I feel much better now... Gregg "Allen Browne" wrote in message ... Definately agree. Use the AutoNumber. |
#6
|
|||
|
|||
AutoNumber (Primary Key)
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. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Craig Alexander Morrison" wrote in message ... 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 "Gregg Knapp" wrote in message ... Great! Thanks, guys! I feel much better now... Gregg "Allen Browne" wrote in message ... Definately agree. Use the AutoNumber. |
#7
|
|||
|
|||
AutoNumber (Primary Key)
In article ,
says... 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/AS...jstreettech-20 J Street Technology, Inc. Armen _@_ JStreetTech _._ com |
#8
|
|||
|
|||
AutoNumber (Primary Key)
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. -- Slainte Craig Alexander Morrison "Armen Stein" wrote in message ... In article , says... 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/AS...jstreettech-20 J Street Technology, Inc. Armen _@_ JStreetTech _._ com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
autonumber | Deepak | General Discussion | 3 | June 18th, 2004 09:07 PM |
Primary Key not sorted | Mike | Database Design | 6 | June 16th, 2004 11:11 PM |
Getting primary and secondary axes to line up | Varsha | Charts and Charting | 1 | June 5th, 2004 03:33 AM |
AutoNumber for Primary Key | MT DOJ Help Desk | Database Design | 4 | May 15th, 2004 04:52 AM |