By Richard Rost 5 months ago
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?"
There is no Extended Cut for this video
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.
References and Research