changing the primary key value in SQL server 2005

Question

I have a Table which has a Primary Key field which is not set to auto-increment. I want to change one of these primary keys to something different.

The problem arises with the other tables relations. The thing is, the guy who built this system did not build relations in SQL Server, but rather manually coded some override in the program that uses it - a VB 6 program.

How would I update a Primary Key and all instances of the Primary Key in other databases? I have to manually look for the instances(although I do know they are in only two tables) of the Primary Key and change them, but how do I do that?

Answer 1

Hey,

Very carefully :-) One thing I would suggest is using the query:

select * from INFORMATION_SCHEMA.Columns where Column_Name = 'FieldID'

This queries the metadata to see all where that field exists, just in case there are more. Then, just write an update script to change the key, unfortunately its a manual process but being that the relationship is missing it will make scripting easier.

HTH.

Answer 2

Even though the person who first created the tables didn't include foreign keys to them, you can add them now if foreign keys are honored in your tables. When you create the foreign keys, create them with ON UPDATE CASCADE option. This way, when you update your primary key, the related foreign keys will also be updated.