如何更新 SQL Server 中的标识列? [英] How to update Identity Column in SQL Server?

问题描述

我有 SQL Server 数据库，我想更改标识列，因为它已启动有一个很大的数字 10010 并且它与另一个表相关，现在我有 200 条记录，我想在记录增加之前解决这个问题.

I have SQL Server database and I want to change the identity column because it started with a big number 10010 and it's related with another table, now I have 200 records and I want to fix this issue before the records increases.

更改或重置此列的最佳方法是什么?

What's the best way to change or reset this column?

推荐答案

您无法更新标识列.

SQL Server 不允许更新标识列，这与您可以使用更新语句对其他列执行的操作不同.

SQL Server does not allow to update the identity column unlike what you can do with other columns with an update statement.

尽管有一些替代方法可以实现类似的要求.

Although there are some alternatives to achieve a similar kind of requirement.

• 当需要为新记录更新标识列值时

使用 DBCC CHECKIDENT 检查当前标识值对于表，如果需要，更改标识值.

DBCC CHECKIDENT('tableName', RESEED, NEW_RESEED_VALUE)

• 当需要更新现有记录的标识列值时

使用 IDENTITY_INSERT 允许将显式值插入到表的标识列中.

SET IDENTITY_INSERT YourTable {ON|OFF}

示例:

-- Set Identity insert on so that value can be inserted into this column
SET IDENTITY_INSERT YourTable ON
GO
-- Insert the record which you want to update with new value in the identity column
INSERT INTO YourTable(IdentityCol, otherCol) VALUES(13,'myValue')
GO
-- Delete the old row of which you have inserted a copy (above) (make sure about FK's)
DELETE FROM YourTable WHERE ID=3
GO
--Now set the idenetity_insert OFF to back to the previous track
SET IDENTITY_INSERT YourTable OFF

这篇关于如何更新 SQL Server 中的标识列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！