6 Replies Latest reply on May 23, 2014 2:59 PM by SteveMartino

    Primary/Foreign Keys--Numbers or Text



      Primary/Foreign Keys--Numbers or Text


           Hello forum (Hi Mr. Phil).  I was going thru my database, tidying up and noticed that some of the primary keys are numbers and some are text.  All the relationships work fine.  What I need is for the ID's to be 8 digits (to work with exporting to another system), so I set them up that way.  But, when I make a new related record (say from Customer PK# 00000123), it shows on the related record as 123.  If I change them all to text, will it destroy the relationship and the related records?

           I was going to make a copy of the DB and give it a try, and spend a lot of time checking it, but was curious if either a) it works, or b), don't do it.

           Any thoughts would be greatly appreciated.


        • 1. Re: Primary/Foreign Keys--Numbers or Text

               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.

          • 2. Re: Primary/Foreign Keys--Numbers or 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?

            • 3. Re: Primary/Foreign Keys--Numbers or Text

                   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.

              • 4. Re: Primary/Foreign Keys--Numbers or Text

                     Thanks.  Corrected per your response, and thoroughly checking records.

                     Always appreciative.


                     p.s.Why were you up so early?

                • 5. Re: Primary/Foreign Keys--Numbers or Text

                       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.

                  • 6. Re: Primary/Foreign Keys--Numbers or Text

                         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