SQL Server Changing primary key value for a database row

mwg9r5ms  于 2023-04-10  发布在  其他
关注(0)|答案(2)|浏览(149)

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.

ubbxdtey

ubbxdtey1#

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.

dz6r00yl

dz6r00yl2#

Do not do this!

What you are trying to do is actually a SQL antipattern.

Even a deleted ID contains information (i.e., that a record has been deleted), and you should therefore not reuse IDs. Moreover, if the old ID is still referenced in another table (or in the notebook of the old-fashioned guy next door), troubled waters are ahead.

I highly recommended the book SQL Antipatterns . It contains a more detailed description of this antipattern (as well as lots of others). Note: I am not affiliated with the authors of that book.

相关问题