Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How do I maintain referential integrity when updating lookup tables

Database Programming

How do I maintain referential integrity when updating lookup tables

by  hilfy  Posted    (Edited  )
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:
Code: 
  update MasterTable
  set CodeField = NewValue
  where CodeField = OldValue
3. Delete the record with the old code from the lookup table.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top