2 Replies Latest reply on Aug 26, 2014 8:27 AM by philmodjunk

    Creating Unique Primary and Foreign Keys After the Fact

    themactech.

      Title

      Creating Unique Primary and Foreign Keys After the Fact

      Post

           Hi All

           If this has been discussed before please forgive me and point me to a more appropriate thread.

           I just inherited a simple "one to many" 2-table database (but has over 700 records) so far. The PK was set as a text field called Catalog Number which was related to a FK (text) named the same thing in the other table.

           The problem i discovered was that the PK in the catalog table wasn't set to be unique or auto-entered so as a result I have duplicate catalog numbers in the primary table with different data.

           I know how to create a new pkID field that is auto-entered and then populate it.. but what about the other table. If I create the fkID field in the other table, what's the best practice to make sure i have the most accurate data on the other side?

           Hope that makes sense and thanks in advance for the advice.

           CL

        • 1. Re: Creating Unique Primary and Foreign Keys After the Fact
          j.hall

               I think a script may be your best solution here. Have it look up the catalog number in the 2nd table and then reference back to the first and update the FKID based on what it finds. Then when it's done that, you can reconfigure your relationship to use the pkID <-> fkID instead of the Text based Catalog number.

               Another option, which might also work for you that I've had to do, is to "retype" the catalog number. Doing a GetAsNumber(CatalogNumber) and that will convert it to a new field as a Number instead of a text. Keep in mind that won't work if the catalog number has anything other than numbers in it.

               I'm sure others may have a better solution, but that's my input on it.

          • 2. Re: Creating Unique Primary and Foreign Keys After the Fact
            philmodjunk

                 The key thing is to assign the new correct value to your FK fields before you make a change that breaks the current relationship. That may require setting up a temporary additional table occurrence of your parent record and even a field to hold the new primary key that is separate from the original catalog number field.