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 relations 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 try 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 temporary suppress foreign key constraints or is there a better way to change Id for person?

Answer 1

First off, changing the primary key value is never a good idea. Your main focus should be to try and avoid that by all means.

If you cannot eliminate the need to update the primary key value, then your best bet would be to define the foreign key relationship between those two tables to be using ON UPDATE CASCADE, so that any changes to the main table's primary key will be automatically cascaded down to the child table.

To do this, drop your existing foreign key relationship and then add:

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

That should then automatically update the Person2Address table's PersonId value if the Id on the person changes.

Now you should be able to just call

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

and that should be all there is!