如何以编程方式更改身份列的值？

Question

我有一个MS SQL 2005数据库，其中有一个名为Test的表，它有一个ID列。 ID是自增列。

在这个表中，我有一些已经有了其对应ID自增值的行。

现在我想要改变这个表中每个ID的值：

ID = ID + 1

但当我这样做时，我会得到一个错误：

Cannot update identity column 'ID'.

我尝试过这样做:

    ALTER TABLE Test NOCHECK CONSTRAINT ALL 
    set identity_insert ID ON

但这并没有解决问题。

我需要将标识设置为此列，但有时也需要更改值。因此，我的问题是如何完成此任务。

- Tom Smykowski

Answer 1

你需要...

set identity_insert YourTable ON

然后删除你的行，并使用不同的身份重新插入它。

插入完成后，不要忘记关闭identity_insert

set identity_insert YourTable OFF

- A-K

---

仅在插入数据时设置才有效，更新数据时无效。UPDATE语句仍将失败。 - Huske

更新需要删除并重新插入，没有其他方式。 - ashes999

@MartinSmith 您的解决方案似乎太长了。能够通过两个步骤（关闭标识，删除/插入，打开标识）来完成，这更为有效。 - ashes999

那是四个步骤，不是两个。我的回答只多了一个（创建表、切换、更新、切换回来、删除）。你可能更熟悉这些步骤，所以它们看起来不那么复杂。当涉及到许多行时，更新可以比删除插入更有效率。此外，你在哪里保存已删除的行？如果是 #temp 表，然后再删除，那就是你没有计算在内的另一个步骤。 - Martin Smith

Answer 2

IDENTITY列的值是不可变的。

然而，可以将表的元数据切换以删除IDENTITY属性，进行更新操作，然后再切换回去。

假设以下结构：

CREATE TABLE Test
(
ID INT IDENTITY(1,1) PRIMARY KEY,
X VARCHAR(10)
)

INSERT INTO Test 
OUTPUT INSERTED.*
SELECT 'Foo' UNION ALL
SELECT 'Bar' UNION ALL
SELECT 'Baz'

那么你可以这样做

/*Define table with same structure but no IDENTITY*/
CREATE TABLE Temp
(
ID INT PRIMARY KEY,
X VARCHAR(10)
)

/*Switch table metadata to new structure*/
ALTER TABLE Test SWITCH TO Temp;

/*Do the update*/
UPDATE Temp SET ID = ID + 1;

/*Switch table metadata back*/
ALTER TABLE Temp SWITCH TO Test;

/*ID values have been updated*/
SELECT *
FROM Test

/*Safety check in case error in preceding step*/
IF NOT EXISTS(SELECT * FROM Temp)
    DROP TABLE Temp /*Drop obsolete table*/

在 SQL Server 2012 中，可以使用 SEQUENCES 来更加简单地更新自动递增列。

CREATE SEQUENCE Seq
    AS INT
    START WITH 1
    INCREMENT BY 1

CREATE TABLE Test2
(
ID INT DEFAULT NEXT VALUE FOR Seq NOT NULL PRIMARY KEY,
X VARCHAR(10)
)

INSERT INTO Test2(X)
SELECT 'Foo' UNION ALL
SELECT 'Bar' UNION ALL
SELECT 'Baz'

UPDATE Test2 SET ID+=1

- Martin Smith

---

非常流畅。每天学习新的东西（顺便说一句，我在SQLExpress上测试过这个；尽管进行了SWITCH TO，但显然它并没有使用分区）。请注意，表格必须非常精确地匹配（索引、FK等）。 - Mark Sowul

当您在原始表上建立了PK、FK、Index和Views时，switch方法是否有效？使用此方法会导致它们中断吗？ - t.j.

在创建临时表脚本时，请将源表及其所有索引和约束一并包含。然后更改表和约束的名称以避免冲突。视图不会造成问题，除非被索引或绑定到模式。 - Martin Smith

如果表上有全文搜索索引，则无法正常工作。我想可能可以先删除它，然后重新创建，但我还没有测试过。 - youen

Answer 3

通过 SQL Server 2005 管理器中的 UI，更改列以删除该列的自动编号（identity）属性（通过右键单击表并选择“设计”）。

然后运行你的查询：

UPDATE table SET Id = Id + 1

然后，去将自动编号属性重新添加到该列。

- Michael Pryor

---

如果您要手动进行更改，可以要求经理为更改生成SQL脚本（在表设计器菜单中选择“生成更改脚本”）。对于此更改，它将创建一个新表并复制数据，然后删除原始表。 - Robin Bennett

@tomaszs- 一个代码示例，也更有效率，因为它根本不需要物理重建表格（这将使其两次重建），在我的晚些回答这里中。 - Martin Smith

你不能再添加标识。你这么做了吗？https://dev59.com/K3NA5IYBdhLWcg3wNrAy - Tomas Kubes

谢谢。非常完美。我的表中有一行我不知道的数据，所以我的表格引导脚本中的“Id”自动标识值偏移了1。 - Shiva

Answer 4

首先，开启或关闭IDENTITY_INSERT设置不适用于您所需的情况（它用于插入新值，例如填补空缺）。

通过图形用户界面执行操作只会创建一个临时表，将所有数据复制到没有标识字段的新表中，并重命名该表。

- Miles D

Answer 5

这可以通过使用临时表来完成。

思路

• 禁用约束（以防您的 id 受到外键引用）
• 创建一个带有新 id 的临时表
• 删除表内容
• 将复制表中的数据复制回原始表
• 启用先前禁用的约束

SQL 查询

假设您的 test 表具有两个附加列 (column2 和 column3)，并且有 2 张表引用了 test 的外键，分别称为 foreign_table1 和 foreign_table2 （因为现实生活问题从未简单）。

alter table test nocheck constraint all;
alter table foreign_table1 nocheck constraint all;
alter table foreign_table2 nocheck constraint all;
set identity_insert test on;

select id + 1 as id, column2, column3 into test_copy from test v;
delete from test;
insert into test(id, column2, column3)
select id, column2, column3 from test_copy

alter table test check constraint all;
alter table foreign_table1 check constraint all;
alter table foreign_table2 check constraint all;
set identity_insert test off;
drop table test_copy;

就是这样了。

- Manitra Andriamitondra

Answer 6

DBCC CHECKIDENT（‘databasename.dbo.orders’，RESEED，999）可以使用此命令更改任何标识列号码，并且还可以从您想要的任何数字开始该字段编号。例如，在我的命令中，我要求从1000（999 + 1）开始。祝好运！

- Roxana

Answer 7

如果该列不是主键，您可以在表中创建一个新的带有递增数字的列，删除原始列，然后将新列更改为旧列。

好奇为什么您需要这样做... 我所做的大多数标识列操作都涉及到填充数字，我最终使用了DBCC CHECKIDENT（tablename，RESEED，newnextnumber）。

祝你好运！

- Christopher Klein

Answer 8

首先将所有ID保存下来，通过编程方式将它们改为所需的值，然后从数据库中删除它们，最后再使用类似以下代码的方式重新插入它们：

use [Name.Database]
go
set identity_insert [Test] ON
insert into [dbo].[Test]
           ([Id])
     VALUES
           (2)
set identity_insert [Test] OFF

使用批量插入：

use [Name.Database]
go
set identity_insert [Test] ON
BULK INSERT [Test]
FROM 'C:\Users\Oscar\file.csv'
WITH (FIELDTERMINATOR = ';',
      ROWTERMINATOR = '\n',
      KEEPIDENTITY)
set identity_insert [Test] OFF

来自file.csv的示例数据：

2;
3;
4;
5;
6;

如果您不将identity_insert设置为关闭，您将会收到以下错误提示：

当IDENTITY_INSERT设置为OFF时，无法在表“Test”中插入标识列的显式值。

- Ogglas

Answer 9

您可以插入带有修改后的值的新行，然后删除旧行。以下示例将ID更改为与外键 PersonId 相同。

SET IDENTITY_INSERT [PersonApiLogin] ON

INSERT INTO [PersonApiLogin](
       [Id]
      ,[PersonId]
      ,[ApiId]
      ,[Hash]
      ,[Password]
      ,[SoftwareKey]
      ,[LoggedIn]
      ,[LastAccess])
SELECT [PersonId]
      ,[PersonId]
      ,[ApiId]
      ,[Hash]
      ,[Password]
      ,[SoftwareKey]
      ,[LoggedIn]
      ,[LastAccess]
FROM [db304].[dbo].[PersonApiLogin]
GO

DELETE FROM [PersonApiLogin]
WHERE [PersonId] <> ID
GO
SET IDENTITY_INSERT [PersonApiLogin] OFF
GO

- Tomas Kubes

Answer 10

身份标识修改可能会因许多因素而失败，主要与链接到id列的对象/关系有关。这似乎是数据库设计的问题，因为 id 应该很少甚至从不更改（我相信你有你的原因并且正在级联更改）。如果您真的需要不时更改 id，请建议创建一个新的虚拟 id 列，它不是主键/自动编号，您可以自己管理并从当前值生成。或者，如果您遇到允许身份插入的问题，则上面的 Christopher's 想法也是我的另一个建议。

祝你好运

附言：它之所以失败，不是因为顺序正在尝试将列表中的值更新为已存在于id列表中的项目吗？也许的确在猜测，可以添加行数+1，然后如果可以请减去行数:-S

- Tanner