Database Reference
In-Depth Information
POWER USERS' CLINIC: REFERENTIAL INTEGRITY
By now, it's ingrained in your developer's brain that relationships work because there's a match
between key fields in the related tables. But what if you absolutely, positively
have
to change the
value in a key field? Say you inherit a file that uses telephone numbers as key fields, and you want
to bring the database up to par by using a serial number field instead. You know it'll break up rela-
tionships, because as soon as you change the value in the “one” side of the relationship, all the “to-
many” records are no longer related to their parent records (or to any other record). In other words,
they're orphaned.
If you changed the value in key fields manually, it'd be fairly easy to figure out how to keep this
from happening. You use the existing relationship to find the child records, change their keys, and
only then go back to the parent record and change its key. The record family is reunited, and every-
body is happy.
Here's a script that handles that grunt work for you:
Allow User Abort [Off]
Go to Layout ["Customers" (Customers)]
Set Variable [$newID;
Value:Customers::NewCustomerID]
Go to Related Record [Show only related
records; From table:"Jobs"; Using
layout:"Jobs"(Jobs) ; New window]
If [not IsEmpty (Jobs::Customer ID)]
Loop
Set Field [Jobs::Customer ID; $newID]
Go to Record/Request/Page [Next; Exit
after last]
End Loop
Close Window (Name: "Referential Integrity") ; Current File]
End If
Set Field [Customers::Customer ID; $newID]
There's some brainwork that this script doesn't handle, like making sure your NewCustomerID
value is unique before you use it. If you're changing your key field value, it's probably not a surrog-
ate key (
Choosing a Good Primary Key Field
)
, so you'll have to know how your business policy
creates and ensures unique key values and then apply that logic to your script. And you need to en-
sure that each related record is unlocked and available for your script to change (
Opening, Revert-
ing, and Committing Records
)
. Using this script as a foundation, add the logic you need in the ap-
