Changing primary key value for a database row

Question

I am using SQL Server Management Studio and I want to change an auto increment primary key value of a table row, with a different value. SQL Server Management Studio after opening the table for edit, shows this field grayed for all rows, of course.

Is it possible? I want to use the number of a row we deleted by mistake, therefore it's valid (there is no conflict with other primary key values) and - most important of all - the next row added in the DB should have an intact auto incremented value.

Thanks.

EDIT: losing the link with other table records on this PK is not an issue for this row. We can restore it manually.

Answer 1

Not necessarily recommended but, insert a copy of the row where you want to change the number, but with the ID you require:

SET IDENTITY_INSERT aTable ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO aTable (id, product) VALUES(3, 'blah')       
GO

SET IDENTITY_INSERT aTable OFF
GO

Then delete the row with the number you don't want (after you update any FK references).

More details here: http://technet.microsoft.com/en-us/library/aa259221(v=sql.80).aspx

---

For posterity, to clarify the question in the comment below, the auto increment value will only be affected if you insert a value greater than the current maximum.

Quoting from linked article:

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.