重新编号主键

Question

我该如何重置SQL表的主键计数器并更新每行为新的主键？

- Aaron Fischer

---

主键不需要重新编号，因为数字本身无关紧要。数字1和32以及212479817423一样好用。否则，你就建立了一个智能主键。 - Mark Brady

请记得解决外键损坏的问题。 - DOK

我需要进行重新编号，因为int类型的数值只能达到一定的大小，而且我已经接近它的限制了。关键字中存在很多碎片化的情况，所以重新编号会给我提供更多的空间... - djangofan

@Mark Brady，你的评论并没有什么用处。想要重新编号id有许多合法的理由之一是为了消除碎片化，比如当你的空间不足时。请注意，我的翻译尽力使内容保持原意并更加通俗易懂。 - md1337

Answer 1

我会先给表添加另一列，将其用新的主键填充。

然后使用update语句来更新所有相关表中的新外键字段。

然后可以删除旧的主键和旧的外键字段。

编辑：是的，正如Ian所说，您将不得不删除并重新创建所有外键约束。

- Galwegian

---

同意。在执行更新操作之前，您还需要删除任何外键约束，并在更新完成后重新应用它们。 - Ian Nelson

不必删除和创建约束，您可以将其禁用，完成后再启用。这只需要一行SQL代码，请参见此处：https://dev59.com/qHVC5IYBdhLWcg3w21Mq - kristof

Answer 2

不确定您使用的是哪种数据库管理系统，但如果是SQL Server：

SET IDENTITY_INSERT [MyTable] ON

允许您更新/插入主键列。然后，当您完成更新键（如果逻辑复杂，可以使用CURSOR）时。

SET IDENTITY_INSERT [MyTable] OFF

希望这能有所帮助！

- Zachary Yates

---

这个答案对我很有帮助（+1），只是想说它是IDENTITY_INSERT。 - Jonathan

Answer 3

这可能与 MS SQL 有关，但不一定是专属的： TRUNCATE TABLE 会重置 identity 计数器，因此一种快速且简单的方法是： 1）备份 2）将表内容复制到临时表中： 3）将临时表内容复制回具有 identity 列的表：

SELECT Field1, Field2 INTO #MyTable FROM MyTable

TRUNCATE TABLE MyTable

INSERT INTO MyTable
(Field1, Field2)
SELECT Field1, Field2 FROM #MyTable

SELECT * FROM MyTable
-----------------------------------
ID    Field1    Field2
1     Value1    Value2

- AugustLights

---

但这会失去所有外键关系。 - James Curran

我之前没有意识到 TRUNCATE 的这个特性。很酷。 - Chris Van Opstal

关于外键，你说得对，但这并没有明确要求。如果有外键指向该主键，无论如何都无法截断。 - Codewerks

Answer 4

你为什么要费心呢？计数器为基础的“身份”主键的整个意义在于数字是任意和无意义的。

- Tony Andrews

---

说真的，这让我很苦恼。我有几个保存数据的表格，在开发过程中这些数据会发生变化。在交付之前，我希望主键从1开始并连续。我不想让客户看到我做了什么改动。 - Aaron Fischer

你能不能只是删除并重新创建表格，因为在发货之前你可能想要将它们清空？ - JohnFx

你为什么在意他为什么想要这样做？他可能有一个非常合理的需求。 - Iain Holder

还是为了隐藏糟糕的数据库管理员做法，例如删除记录？嗯？ - Fandango68

Answer 5

你可以按照以下步骤完成：

1. 创建一个带有额外列 new_key 的 yourTable 的副本。 2. 将受影响的行以及 new_key 的所需值填充到 copyOfYourTable 中。 3. 暂时禁用约束。 4. 更新所有相关表，将其指向 new_key 的值而不是 old_key 的值。 5. 从 yourTable 中删除受影响的行。 6. 执行 SET IDENTITY_INSERT [yourTable] ON。 7. 使用副本表中的新键值重新插入受影响的行。 8. 执行 SET IDENTITY_INSERT [yourTable] OFF。 9. 重新设置标识。 10. 重新启用约束。 11. 删除 copyOfYourtable。

但正如其他人所说，这些步骤并非必要。我倾向于将自增主键视为 C 语言中的指针，我使用它们引用其他对象，但不直接修改或访问它们。

- kristof

---

很遗憾，“使用new_key的值更新old_key”这部分是错误的，您无法更新标识列。您只能插入它。而且SET IDENTITY的语法是错误的，应该是SET IDENTITY_INSERT（下划线）。 - md1337

Answer 6

1. 将表导出为sql文件。
2. 然后从*.sql文件中复制插入查询，并将所有自增字段设置为'NULL'。
3. 清空表，然后运行修改后的查询。

- Shaji Joseph

Answer 7

如果这是微软的SQL Server，您可以使用[dbcc checkident](http://msdn.microsoft.com/en-us/library/ms176057(SQL.90).aspx)来解决问题。

假设您有一个要在其中移动数据并重新编号主键的单个表。例如，表的名称为ErrorCode。它有两个字段，ErrorCodeID（它是主键）和Description。

使用dbcc checkident的示例代码

-- Reset the primary key counter
dbcc checkident(ErrorCode, reseed, 7000)

-- Move all rows greater than 8000 to the 7000 range
insert into ErrorCode
select Description from ErrorCode where ErrorCodeID >= 8000

-- Delete the old rows
delete ErrorCode where ErrorCodeID >= 8000

-- Reset the primary key counter
dbcc checkident(ErrorCode, reseed, 8000)

通过这个例子，您将有效地将所有行移动到另一个主键，然后重置，以便下一个插入采用8000 ID。

希望这有点帮助！

- Scott Saad