AutoNumbers in Microsoft Access: Good or Bad? Should you use an AutoNumber for your Primary Key?

I hear a lot of criticisms and arguments against using AutoNumbers as the primary key field in Microsoft Access databases. In this video, I will examine exactly what AutoNumbers are, when they should be used, when you might not want to use them, what those arguments against them are, and of course, my counters to those arguments.
Craig asks, "I’m taking a college course and the instructor gave us a homework assignment. He wants us to create a table to store vehicles where the Primary Key is the VIN. I thought you said to always use AutoNumbers. Who’s right here?"
TOPICS COVERED
- What is a Key Field
- What is a Primary Key Field
- Primary and Foreign Key Fields
- Types of Keys: Natural vs. Synthetic (Surrogate)
- What is an AutoNumber
- Why Use AutoNumbers
- When NOT To Use AutoNumbers
- The German Tank Problem
- Arguments Against AutoNumbers (and my counters)
- Random AutoNumbers and GUIDs
CONCLUSION
AutoNumbers are easier to use than natural keys or text. Access maintains them automatically and they provide a more stable database than Natural Keys. Generally, AutoNumbers save disk space and increase database performance. They decrease total cost of ownership. Other options are available to provide the user with visible identifiers for records, such as order numbers, so as not to give up business intelligence, however an Autonumber should be the primary key for MOST tables you build, with few exceptions.
SIDE VIDEOS
Create Your Own Customer Code: • Create Custom Microsof...
Sequential Numbers for Invoices: • Microsoft Access Creat...
Random AutoNumbers to Sync Remote Databases: • How to Sync Up a Remot...
Restore Lost or Deleted AutoNumbers - • Microsoft Access How t...
RESOURCES & REFERENCES
en.wikipedia.org/wiki/Surroga...
stackoverflow.com/questions/2...
en.wikipedia.org/wiki/German_...
access.mvps.org/access/tencomm...
www.fmsinc.com/MicrosoftAcces...
en.wikipedia.org/wiki/Univers...
www.fmsinc.com/free/newtips/P...
en.wikipedia.org/wiki/Christo...
www.amazon.com/Relational-Dat...
www.agiledata.org/essays/keys....
answers.microsoft.com/en-us/m...
en.wikipedia.org/wiki/Univers...
www.sqlservercentral.com/arti...
FURTHER LEARNING
FREE Customer Template Database: 599cd.com/XCDT
FREE Access Level 1: 599cd.com/Free101
$1 Access Level 2: 599cd.com/1Dollar
Your Questions Answered: 599cd.com/TechHelp
Learning Connection 50% OFF: 599cd.com/Connection
Richard's Blog: 599cd.com/blog
Access Forum: 599cd.com/AccessForum
Twitter: / learningaccess
Email Me: [e-mail]
en.wikipedia.org/wiki/Microso...
products.office.com/en-us/access
microsoft.com/en-us/microsoft...

[@oppositeistrue]

I think that all Richard's videos are great, but this one was perfect.

[@599CD]

Thanks, Luke. I appreciate that!

[@JaguarPGP]

Great video, thank you. I just migrated some small tables from Access to SharePoint lists, and it renumbered my autonumber field, which was an unwelcomed surprise. Luckily it was only 40 records that needed updating, but if there were a lot of records, I could see that being a headache.

[@599CD]

Indeed!

[@anti-vazinotanti-vaxi5859]

Agreed. I have tables with up to 6 attributes which determines the logical ('natural') primary key. Imagine ALL related tables must have an additional 6 attributes (which the related table will NEVER provide for actual use) - to ensure the relationship. Autonumber almost always works best. (You can STILL ensure uniqueness on the 'one' table, of those 6 fields - which effectively STILL makes it a 'primary' key).

[@599CD]

Awesome. Thanks for sharing.

[@wsitech]

I'm 50/50 on this one. I tend to use "natural" primary keys when they're integers. VIN or SSN are not numbers, and SSN certainly wouldn't be stored as a number. They're unique but I can see the argument that they would be difficult to use as foreign keys. I find it interesting that Microsoft's own Northwind database uses 5-character code as a company ID. I'd say I use Autonumbers for PK about 90-95% of the time. One of the exceptions is in connection tables for M-to-M relationships if the connecting fields are both integers. In that case I make the two foreign keys a joint primary key and be done with it, instead of having to add an index. IMHO adding an autonumber to that table is rather superfluous. I also had a client once where we we had a Work Orders table and the client wanted to use artificial primary key numbers because there were a lot of orders entered in error and since autonumbers aren't re-used, we'd end up with a lot of skipped numbers and the IDs would get out of hand really quickly. Generating the numbers manually was much more of a pain and led to some other difficulties but at least WO #1035 could be re-used if a previous WO#1035 was deleted right away because the WO was added by accident.

[@599CD]

Interesting. Thanks for sharing! :)

[@anti-vazinotanti-vaxi5859]

There are NO instances where autonumber should not be used. If you use them in one table, then use them in all. M-to-M relationships: create a link table to resolve those (problem) many-to-many relationships. This is NO different to adding a FK attribute to a related table - you simply add 2 PK values, in 2 FK attributes in the 'link' table (and yes - make them both a 'composite' key). I also 'tend' to ALWAYS add an autonumber called PK - regardless. What is the problem with adding an autonumber PK to a link table? None. And, when it comes to you 'teaching' someone - the rule is simple: "ALWAYS ADD A AUTONUMBER PK TO EVERY, SINGLE TABLE". Now, there is absolutely no need to decipher what to do - you ALWAYS DO IT. This ensures consistency - everyone who opens my DB naturally understands this (and hasn't to decide which method I've used). I ALWAYS name that attribute 'pk', in EVERY table, and I ALWAYS name the related table foreign key to it: [tablename_fk] - so that it is immediately obvious that it is a foreign key, and, to which table it relates (which is useful when you have many foreign keys in a table). ;-)

[@bayanmobarak4661]

Hello Rirchard, Thanks for your great content! I have a question regarding the relationships. I have more than one table with an autonumber primary key on it, when I linked the tables (relationships) and made a form, I didn`t find that the values stored on the primary key reflected on the foreign key fields, can you help me in this ? I assigned data type of the foreign key as Number. Thank u in advance

[@599CD]

Are you using a form with a subform? If not, you're not doing it correctly. Never use two joined tables in a single form. 599cd.com/Subform

[@shafiulamin4128]

Hi Richard, top explanation , thank you. It may sound as a trivial question. I am trying to understand - how autonumber id of a table where it is primary key get stored/generated in the child table where it is a foreign key. So in the child table these numbers are not autonumber for sure. So how do you assign these numbers in the child table. I am trying to relate this scenario where two tables get imported and appended for reporting. And there is a relationship between these two tables. Can you make a vedio on this please?

[@599CD]

Normally if you're adding data yourself manually inside of Access, you set up a form with a subform. The subform control itself maintains this relationship with Link properties. Importing data is a whole different can of beans. You would need some kind of a field in your import data that knows how to make the relationship. Can you give me an example of what you're trying to do?

[@shafiulamin4128]

@@599CD Hi Richard, very kind of you to take the time to reply. Regarding providing you an example , I using a database as an end user and therefore not familiar with the technically yet. But I keep watching your videos . Needless to say I am learning a lot from your videos. Hopefully will join your website very soon. God bless you!

[@aslamnoori]

Incidentally my project is dealing with Automobile trade where VIN is unique. If I assign an AutoNumber primary key in purchases and Sales table, which field will be foreign key in corresponding tables? We in Excel put Invoice number in Sales file which tracks rest of all fields for customer balance and stocks in trade. I am really confused to design Relationship and the foreign keys in front of primary key of AutoNumber.

[@599CD]

Use the AutoNumber for your relationships. It's the PK in your Vehicle table, and the FK everywhere else. Still keep that VIN around, sure, and index it (no duplicates), but don't use it to make relationships.

[@aslamnoori]

@@599CD Still not clear Sir. AutoNumber will be PK in Vehicle Table. Okay. Which field will be FK in other tables?

[@fuego09esmeralda]

Then again, to answer Craig's question, if you're taking a college course and your instructor asks you to use the VIN as a primary key AND you want to have a good grade in that class, you should do exactly what the instructor asks... Once you pass the class and are designing databases for real life applications, then follow Richard's excellent advice... 😀

[@599CD]

You, sir, are absolutely correct. I was always the kid in school who would argue with the teacher, even when I was wrong. My grades reflected that. :)

[@anti-vazinotanti-vaxi5859]

@@599CD Same here. A shame that lecturers ears don't prick up when a student questions their logic.