Access Explained: AutoNumbers Good or Bad? Choosing Primary Keys Without Starting a War

If you've spent any time around database folks, you already know there are a few topics that can turn into a full-on Klingon batleth fight. One of them is primary keys. And in the Access world, the classic version goes like this: "Should my primary key be an AutoNumber, or should I use something meaningful like a VIN, email address, or invoice number?" Today's episode exists because this question keeps coming up, and I want to save you from learning it the hard way at 2:00 AM with a broken relationship window and a sinking feeling in your gut.

A recent example: someone taking an Access class in school emailed me and said their teacher told them to make the VIN the primary key for a vehicle table. VINs feel like they should be perfect keys. They're unique. They're real-world identifiers. They're already on the car. So why not? Because "unique" is only one requirement for a good primary key. In practice, you want your primary key to be small, stable, boring, and meaningless. And that, in a nutshell, is why I almost always recommend AutoNumbers as the primary key for most Access tables.

First, quick definitions, because this is where people start talking past each other. A key field is any field that uniquely identifies a record. A primary key is the one key field you choose as the official identifier for relationships. A foreign key is the field in a related table that stores the primary key value from the parent record. That last part matters because the primary key value doesn't just live in one table. It gets copied into other tables over and over as relationships grow.

Now imagine this: your vehicle table has VIN as the primary key. You log trips in a trip table. Every trip record needs to store the vehicle identifier. If you log 10,000 trips, you now store that VIN 10,000 times as the foreign key. VINs are 17 characters. That is a lot of duplicated text. It isn't just wasted space. Bigger keys mean more storage overhead, slower joins, slower indexes, and more data to push around, especially in multi-user setups.

By contrast, an AutoNumber in Access is a Long Integer under the hood. That's 4 bytes. It's fast. It's compact. It's ideal for relationships and lookups. And because it's generated by the system, it doesn't carry business meaning, which is exactly what you want for a relationship key. Let the database manage identity. Let your business fields describe the real world. This is why, in my lessons, I teach my students to use AutoNumbers for primary keys and keep real-world identifiers (like VINs) as separate, indexed fields.

This is also where "natural key" versus "synthetic key" comes in. Natural keys are real-world identifiers like VIN, email address, phone number, product code, or social security number. Synthetic keys are system-generated identifiers like CustomerID, VehicleID, EmployeeID. Synthetic keys are also called surrogate keys, blind keys, and a bunch of other names, but the important part is they are meaningless and stable.

Stability is the part that bites people. Natural keys can change. Phone numbers change. Email addresses change. Product codes get revised. Company policies change. Sometimes the "perfect" key turns out to be not so perfect two years later when management decides invoice numbers need a prefix, or your company merges with another one and their employee codes aren't numeric anymore. If you used the natural key as the primary key, you now have a cascade of changes and a bunch of related tables that must be updated perfectly. And yes, cascade updates can help in some cases, but once you're in real life with split databases, linked tables, or upscaling to SQL Server, assumptions start breaking and things get complicated fast.

AutoNumbers also upscale nicely. If you ever move your backend tables from Access to SQL Server, AutoNumbers map cleanly to identity or sequence-style fields. Using the same basic key strategy across your tables reduces confusion and cuts down on the "Wait, what does this table use as its key again?" mistakes. Those mistakes are the ones that make you question your career choices.

So if AutoNumbers are so great, when should you not use them?

If the identifier must be human meaningful and visible, don't use an AutoNumber for that visible identifier. AutoNumbers should not be customer-facing. If you want invoice numbers, order numbers, customer codes, membership IDs, whatever, make a separate field for that. AutoNumbers are internal plumbing. Don't show people your plumbing unless you enjoy awkward conversations and expensive repairs.

Also, if you truly need strict sequential numbering without gaps, AutoNumbers are not the right tool. They can have gaps. Records get deleted. Transactions get rolled back. Life happens. If your accountant needs invoice numbers that go 1001, 1002, 1003 with no gaps, you need a different mechanism. Keep the AutoNumber as the primary key, and create a separate sequential field for the business requirement.

And here's the underrated, slightly paranoid, but very real reason: AutoNumbers can leak business intelligence. If you show customers an invoice number that's literally your internal AutoNumber, you're telling them roughly how many invoices you've created. This is the same basic idea as the German tank problem from World War II, where serial numbers can be used to estimate production counts. If you don't care about that, fine. If you do care, don't expose sequential internal IDs.

There are some niche cases where you might skip an AutoNumber. Small lookup tables that never relate to anything else. Temporary tables you create for one-time processing. Edge cases where you want to minimize indexes because you're doing massive write-only logging and almost no searching. But those are exceptions. Most real databases benefit from having a compact, stable, meaningless key that the system maintains.

One last myth worth clearing up: "If you delete a record, you can never get that AutoNumber back, so your related child records are doomed." In normal day-to-day use, you should treat deleted AutoNumbers as gone. But if you have backups, you can often restore deleted records with their original IDs using append techniques. The bigger lesson here is not "learn clever tricks." The bigger lesson is: make backups, and don't delete important records in the first place. Mark customers inactive. Mark orders cancelled. Soft delete beats panic restore every time.

So where does that leave the original argument?

If you're in a class, do what your instructor wants and get your grade. In the real world, my recommendation is usually this: use an AutoNumber as the primary key, store the VIN as a separate field, and index that VIN as no-duplicates if it must be unique. You get the best of both worlds. Access gets a fast, compact relationship key. You get a real-world identifier you can search and validate. And your database stays flexible when the real world inevitably changes its mind.

If you want to see specific topics covered in this series, tell me what you'd like next. I've got plenty of opinions and only a limited supply of self-control, so let's make it count.

LLAP
RR