SQL Server: change primary key with related rows

Question

I want to change the primary key value for one row in a table that has relationships with other tables:

for example

Table Person { Id, Name, +50 fields } Table Address { Id, City, +10 fields } Table Person2Address { Id, PersonId, AddressId } 

I want to change Person.Id and Person2Address.PersonId

I am trying something like:

 BEGIN TRANSACTION UPDATE Pers SET Id = NewId WHERE Id = OldId UPDATE Person2Address SET PersonId = NewId WHERE PersonId = OldId COMMIT TRANSACTION 

But of course it provides conflicts :)

How can I temporarily disable foreign key restrictions or is there a better way to change the identifier for a person?

Answer 1

First, changing the primary key value is never a good idea. The focus should be on trying to avoid it by all means.

If you cannot eliminate the need to update the primary key value, the best choice would be to determine the foreign key relationship between the two tables using ON UPDATE CASCADE , so that any changes to the primary key of the table will automatically be cascaded to the child table.

To do this, undo the existing foreign key ratio, and then add:

  ALTER TABLE dbo.Person2Address ADD CONSTRAINT FK_Person2Address_Person FOREIGN KEY (PersonId) REFERENCES dbo.Person(Id) ON UPDATE CASCADE 

Then, the value of Person2Address table PersonId automatically updated if the Id on the person changes.

Now you can just call

 UPDATE dbo.Person SET Id = NewId WHERE Id = OldId 

and that should be all there is!

Answer 2

Your easiest bet for such things is to use something like:

 BEGIN TRANSACTION UPDATE Pers SET tempId = NewId WHERE Id = OldId UPDATE Person2Address SET tempPersonId = NewId WHERE PersonId = OldId COMMIT TRANSACTION 

Then undo the Id and PersonId fields and rename temp.

Answer 3

You can discard the FK restrictions and recreate them upon completion.

 ALTER TABLE some_table DROP CONSTRAINT my_constraint 

Check this article to create and modify restrictions.