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

    Primary/Foreign Keys--Numbers or Text

    SteveMartino

      Title

      Primary/Foreign Keys--Numbers or Text

      Post

           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.

           Steve

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

               The ideal primary key is:

               Unique

               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
            SteveMartino

                 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
              philmodjunk

                   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
                SteveMartino

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

                     Always appreciative.

                     Steve

                     p.s.Why were you up so early?

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

                       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
                    SteveMartino

                         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