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?
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?
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
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.
aikimark
Other than performance, no.
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.
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 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.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
