Blogger Jeff = new Blogger

Programming and stuff in Western PA

SQL Server 2005 : Updating multiple tables involved in a two column foreign key constraint

I ran in to a fun problem the other day where the database I was working on had a table using two columns for a primary key constraint. This primary key was then involved in a number of foreign key constraints. It looked something like this :

My client then came to me and requested a stored proc that would update one of those columns with a new value. Their Database Administrator insisted that this was no big deal, as simple update statements would do the trick. I of course told them otherwise, because updating one value would blow the constraint in the other tables (and there were like 20 child tables to update). I came up with three different ways to do this :

(1) Disable the constraints. This can be done by altering the constraint to not check the values being inserted :

After running the DBCC command, one row would get returned showing the bad data in the table. Needless to say, I decided to avoid this route in my stored proc.

(2) The next thing I came up with was to do the following :

  • insert data from child tables into a temp table
  • delete data from child tables
  • update parent data with new column value
  • update temp tables with new column value
  • insert temp data back into child tables

Certain problems arise with this solution. If our table schema was more complicated, we might have to do stuff like allow for the insertion of identity values. When this occurs, the insert list has to include all column names, which means any subsequent updates to the table schema would also involve an update to the procedure I’d be writing to include the new columns.

I actually started down this path, but then a simpler option came to me …..

(3) Insert a new row for the Parent, update the Child to the new value of the Parent, and then delete the original Parent. It would go something like this :

This ended up being the solution I went with, as it was the simplest to implement.

Tags:

Advertisements

November 27, 2007 - Posted by | SQL Server, Technology

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: