Are AutoNumbers Good or Bad as Primary Keys in Access?

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?"

Members

There is no Extended Cut for this video

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
• Sequential Numbers for Invoices
• Random AutoNumbers to Sync Remote Databases
• Restore Lost or Deleted AutoNumbers

References and Research

• https://en.wikipedia.org/wiki/Surrogate_key
• https://stackoverflow.com/questions/262547/reasons-not-to-use-an...
• https://en.wikipedia.org/wiki/German_tank_problem
• http://access.mvps.org/access/tencommandments.htm
• https://www.fmsinc.com/MicrosoftAccess/autonumber-field/index.htm
• https://en.wikipedia.org/wiki/Universally_unique_identifier
• https://www.fmsinc.com/free/newtips/PrimaryKey.asp
• https://en.wikipedia.org/wiki/Christopher_J._Date
• https://www.amazon.com/Relational-Database-Writings-1991-1994-Date
• http://www.agiledata.org/essays/keys.html
• https://answers.microsoft.com/en-us/msoffice/forum/all/microsoft-access...
• https://en.wikipedia.org/wiki/Universally_unique_identifier
• https://www.sqlservercentral.com/articles/using-a-surrogate-vs-natural-key

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.