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.
2条答案
按热度按时间ubbxdtey1#
Not necessarily recommended but, insert a copy of the row where you want to change the number, but with the ID you require:
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.
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.