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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.