Mega Code Archive

 

Categories / Delphi / ADO Database

 

How do I maintain referential integrity when updating lookup tables

Title: How do I maintain referential integrity when updating lookup tables Situation: You have a table (lookup table) with two (possibly more) fields Code, Description. The Code field is used in one or more other tables (master table(s)) to point to the description. Problem: You want to change one of the Code fields, but it is already being used in one or more master tables. Solution 1: If there are no referential integrity constraints in the database, you can just update the Code field and update the value for that field in the master tables. Solution 2: If there are referential integrity constraints, you have to go through a little more complicated process (steps MUST be done in this order.) 1. Add a new record to the lookup table with the new code and the old description. 2. Update all of the records in the Master table(s) to have the new code. A simple sql template for this is: update MasterTable set CodeField = NewValue where CodeField = OldValue 3. Delete the record with the old code from the lookup table.