Avatar of Bill Ross
Bill RossFlag for United States of America

asked on 

MS Access PK Text Value

Is there any disadvantage in using a text value as the PK in a  table?  I have always used AutoNumber values as the PK however I am converting data where the PK in the tables is alpha/numeric like AAA1234567.  I thought about using an AutoNumber asnd just looking up the alpha value as needed but there are a number of related tables and I would need to modify them all.

I can create the next value by getting the "AAA" alpha part and just incrementing the 1234567 part so creating the key is not an issue.

Thoughts or suggestions?
Microsoft Access

Avatar of undefined
Last Comment
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of aikimark
Flag of United States of America image

Other than performance, no.
Avatar of PatHartman
Flag of United States of America image

Performance doesn't become an issue until you get to huge row counts unless your text PK is longer than about 10 characters.

The problem in this case is that the PK contains multiple attributes and you will have to constantly split and concatenate the field to work with it.  Separating the constituent parts is better but that gives you a multi-field PK which is a pain to work with and will still force you to update all the sub tables.

Personally, I would go with an autonumber PK.  To do the conversion, you would convert the parent table first to create the PK.  Then for the child tables, you would need to join to the new parent table on the old PK field but append the autonumber value as the FK for the child table.  So once you understand how to do it, the conversion is actually painless.

If the numeric part of the current PK is unique regardless of the alpha part, you can simplify your process and insert the old number part as the PK to the new table.  An append query is the ONLY place where you can actually specify a value for an autonumber.  Without being able to do that, conversions would be a nightmare.  Then, instead of keeping the whole concatenated key in the record, the number would be the PK and the alpha would simply be a data field.
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo