3 Replies Latest reply on Jan 7, 2013 8:23 AM by philmodjunk

    Changing primary (parent) key does not change foreign (child) keys

    DanielNichols

      Title

      Changing primary (parent) key does not change foreign (child) keys

      Post

           Here is my table relationship diagram:

      Primary key - foreign key relationship

           I thought changing Part::Part Name would automatically update all the child Drawings.  Instead, the portal in my Part layout is cleared, and all of those Drawing records are left dangling.

           As far as I can tell, I need to create a script triggered when I enter the Part::Part Name field which saves the current value to a global variable ($$PartName).  Then, after the field has been changed, another script triggered on exit will find all the child records where Drawing::Part Name = $$PartName and set them to Part::Part Name.

           I've been butting my head up against this issue for several hours now, so any help would really be appreciated.

        • 1. Re: Changing primary (parent) key does not change foreign (child) keys
          philmodjunk

               This is by design. The primary and foreign keys are two separate values stored in separate tables. Changing the value in a field in one record will not automatically change the value of a field in the other.

               It would be better to link these two tables by a serial number field in part to a number field in Drawing.

               Part::__pkPartID = Drawing::_fkPartID

               Then changing the the name in Part, does not require any changes to the drawing records.

               Using the relationship that you have in place here, you'll need to change the names in Drawing::Part Name first, then make an identical change to Part::part name. A scritp can make this update using Go to Related Record to isolate the Drawing Records in a found set followed by Replace Field Contents to update the name. In fact, I think this will work without using Go To Related Record. Once you have update drawing::part name, then you can update part::part name to reconnect the related records.

          • 2. Re: Changing primary (parent) key does not change foreign (child) keys
            DanielNichols

                 Is the idea to maintain an unchanging parent-child relating field behind the scenes?  At the end of the day, it sounds like I'll be eliminating the Drawing::Part Name field entirely.

                  

                 Thank you for your help, and thank you for reminding me of the naming convention taught in the Lynda.com videos I watched a few weeks ago.

            • 3. Re: Changing primary (parent) key does not change foreign (child) keys
              philmodjunk
                   

                        At the end of the day, it sounds like I'll be eliminating the Drawing::Part Name field entirely.

                   Given that the data in that field is already present in Part, you are correct. This field becomes redundant once the tables are properly linked by an ID field.