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.