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 | Leave a comment

C# coalesce operator

Damn Microsoft! I finally thought I had my thick head wrapped around C# when they decide to come out with a boatload of new features to further confuse, err, I mean enhance things.

I was quite happy to read that they added a coalesce operator to C#. For those of you who use Sql Server on a regular basis, you’re probably quite familiar with this function. What it does is allow you to pass a list of values, and will return the first non-null value.

For instance, in previous versions of C# we might have code that looks like this :

After running this, the string “brian” gets outputted to the console. We can now rewrite this a little cleaner with the new coalesce (??) operator :

After running this, you’ll see we get the same results. Thanks again to the C# team for giving me the capability to make my code even more unreadable 😉

Tags: , ,

November 23, 2007 Posted by | .Net, C#, Technology | 3 Comments