MS Access PK Text Value

Bill Ross
Bill Ross used Ask the Experts™
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?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
No real problems,   Just the length of the key. With what you have, you'll be fine.

Top Expert 2014

Other than performance, no.
Distinguished Expert 2017

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial