Update autoincrement column in SQL server.

Question

I have a need to change ONE PK value in one row of a table of a SQLSERVER 2005 databsae. The PK is currently auto-incrementing.

One possibility is to temporarily remove the auto-increment from the key, make the modification, and put it back.

The database is in production. Is there a way to safely make this change without taking the DB down?

Why? My customer wants a specific ID for one of the records.

What am I afraid of? I'm afraid of records being added in the DB while I've modified the key, and it somehow messing up an index on another table that uses the field as an FK? Am I nuts?

Answer 1

You can only update via dropping the identity and re-creating it. However, you could also set identity insert ON (http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx) and insert a new row with the identity val you want, turn ident insert off, then delete the old one. Why do you want to do this?

Answer 2

Updating doesn't work - you'd have to insert the new record and delete the old one instead...

set identity_insert mytable on

insert mytable (myidentitycolumn, someothercolumn) 
select 42, someothercolumn
from mytable
where myidentitycolumn = 1;

delete mytable where myidentitycolumn = 1;

set identity_insert mytable off

Answer 3

SET IDENTITY_INSERT yourTable ON

INSERT INTO yourTable (col1, col2, col3)
select newID, col2, col3
FROM yourTable
WHERE currentID = xxx

SET IDENTITY_INSERT yourTable OFF

You may want to lock the table to prevent other inserts from failing while IDENTITY_INSERT is on.

EDIT: Well crap, I forgot the obvious bit about deleting the original record.

Answer 4

Don't forget that any tables which reference this primary key will need to be updated to the new value before you can delete the old row.