The ideal primary key is:
Never, ever changed once assigned to a new record
Devoid of any additional "meaning" beyond that unique identification
As simple and "bullet proof" to implement as possible
Whether the value is text or number is a lesser concern, but I'd not include those leading zeroes in a primary key--that starts to get away from the "simple as possible" rule. Instead, I'd set up a separate calculation field with text as the result type that adds in the leading zeroes and keep the number ID a simple number ID. Right ( "00000" & PrimaryKey ; 6 ) will produce a 6 digit number with up to 5 leading zeroes.
And I put numbers into number fields and text into text fields as consistently as possible. A pure number in a text field won't sort the way you'd expect, for example so that can create issues.
PS. and Get ( UUID ) is an example of an auto-entered primary key that has to be of type text.
Thanks as always for your response Phil. So I guess my main concern, and follow up question is if I change the PK back to a number, will it affect/destroy my relationships?
Make sure to change the matching foreign keys to number as well so that the absence of leading zero characters won't keep related records from correctly matching.
Thanks. Corrected per your response, and thoroughly checking records.
p.s.Why were you up so early?
My day usually starts at 5:30 in the morning, but I was up much earlier today.
Migraine headache + over the counter meds that contain caffeine. That works well for me except for the insomnia that can be produced by the caffeine.
Sorry to hear that. Hopefully you get to relax this extended weekend. I remember you (sort of) like being out in the yard--landscaping/gardening.
edit: oops, clicked the wrong answer as the correct answer