Friday, December 9, 2011

Changing a Primary key value via SQL

I had to run into a rather annoying problem today where due to an error somewhere else in the database I was faced with two tables containing the same Primary Key (AL/1112/0001) which was causing a conflict in my data insertion. The only way to solve it business logic wise was to ensure that one table would programmatically receive the AX prefix and the other AL. While these values are stored in a separate table and changing them was a simple UPDATE statement, changing all the records in a similar way within the tables that would receive the AX prefix in the future was an issue. Changing a primary key value in the following manner:

happens to yield this abominable error due to the constraints and checks enforced

Bah! This may have been just a test DB but reinserting all those values whether through an INSERT/DELETE statement or through the program was going to take time. A quick bit of searching on the MSDN site to see what could be done to alter a table and I got this out.

The first thing you want to do is make all those ‘CHECK’ CONSTRAINTS be switched off. Here’s the code to do that.

I know that the standard code would be ALTER TABLE fcadadet WITH NOCHECK CONSTRAINT ALL but this gives an error with MSSQL. The MSDN documentation specifies it to be put in this way. (Is this wrong?) 

Run your update statement. This time it should work fine.

Once done, don’t forget to put your constraints back on using the code below.

Feel free to try another UPDATE. This time you’ll be met with the error from above again confirming that your constraints are all back in place.

Do note however that this is quite likely a very very bad practice and I used it only because it was a test database and the situation was one of those ‘it doesn’t matter who does what’ things. Using this statement on live data while a system is running is probably a bad idea. Those constraints are there for a reason.

No comments:

Post a Comment