在现有表中为SQL Server添加自动递增主键

Question

如题，我有一个已经填充了150000条记录的现有表格。我添加了一个Id列（目前为空）。

我假设我可以运行一个查询来使用递增数字填充此列，然后将其设置为主键并开启自动递增。这是正确的操作方式吗？如果是，那么我该如何填充初始数字？

- fearofawhackplanet

Answer 1

不行 - 你必须反过来做：一开始就将其添加为INT IDENTITY - 这样它将在添加时自动填充标识值：

ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY

然后你可以将其设置为主键：

ALTER TABLE dbo.YourTable
   ADD CONSTRAINT PK_YourTable
   PRIMARY KEY(ID)

或者如果您更喜欢一步完成所有操作：

ALTER TABLE dbo.YourTable
   ADD ID INT IDENTITY
       CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED

- marc_s

---

这是一个非常好的答案，但我该如何将起始整数从1更改为1000？我想从1000开始计数。我怀疑我可以使用ALTER TABLE ORDER ALTER COLUMN ORDERNO RESTART WITH 1，但我不想在未经专家确认之前尝试它 :) 参考：http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fsqlp%2Frbafysqlpidentity.htm - user1477388

我刚刚使用了这个命令，似乎起作用了：alter table attachments add ATTACHMENT_NUMBER int identity (1000, 1)。 - user1477388

@stom：如果您没有指定任何内容，那么种子=1和增量=1将被使用-这是最常用的设置。如果您创建了这样的表格-它将正常工作。但我建议您在SQL脚本中始终明确指定种子和增量-特别是对于较大的网站。这是一个好习惯。 - marc_s

当你定义主键时，聚集索引会自动为你创建（除非你显式指定为非聚集索引）。 - marc_s

我删除了当前的索引和id列，然后使用这种方法重新添加它。它完美地运行了。 - Jeff Bluemel

Answer 2

你无法“打开”IDENTITY：它是表重建。

如果您不关心编号顺序，可以一次性添加带有IDENTITY的NOT NULL列。150k行并不算多。

如果您需要保留某些编号顺序，则相应地添加数字。然后使用SSMS表设计器设置IDENTITY属性。这使您可以生成一个脚本，该脚本将为您执行列删除/添加/保留数字/重新排列的操作。

- gbn

---

OP正在使用SQL Server 2008 因此有一种方法。 - Martin Smith

整个实例需要在单用户模式下启动，因此在大多数情况下可能不可行，但是ALTER TABLE ... SWITCH可以在不需要这样做的情况下完成。 - Martin Smith

Answer 3

我遇到了这个问题，但由于各种原因无法使用标识列。 我最终采用了这种方式:

DECLARE @id INT
SET @id = 0 
UPDATE table SET @id = id = @id + 1 

本文内容参考自这里。

- Kevin

Answer 4

如果您的表中已经存在该列并且为空，您可以使用以下命令更新该列（将id、tablename和tablekey替换为相应的值）：

UPDATE x
SET x.<Id> = x.New_Id
FROM (
  SELECT <Id>, ROW_NUMBER() OVER (ORDER BY <tablekey>) AS New_Id
  FROM <tablename>
  ) x

- Renzo Ciot

---

你的回答让我省了不少时间！是@gbn回答的很好的补充。 - Kristen Waite

太好了，谢谢伙计。 - undefined

Answer 5

ALTER TABLE table_name ADD temp_col INT IDENTITY(1,1) 
update 

- sandesh jain

---

你能解释一下吗？ - Muhammad Dyas Yaskur

虽然这段代码可能解决了OP的问题，但最好还是解释一下你的代码如何解决OP的问题。这样，未来的访问者可以从您的帖子中学习，并将其应用到自己的代码中。SO不是编码服务，而是知识资源。此外，高质量、完整的答案更有可能被点赞。这些特点以及所有帖子都必须是自包含的要求，是SO作为一个平台的一些优势，使其与论坛区分开来。您可以编辑以添加其他信息和/或使用源文档补充您的解释。 - ysf

我通过谷歌搜索来到这里，因为我只需要语法。对于我来说，这是最好的答案，可能也适用于许多没有SO帐户的其他人。我忽略了被接受的答案，因为它太长了！我的意思是，有两种类型的访客——一种是寻求全面解释的，另一种是将SO用作快速参考的。有一个TLDR部分的答案会很有用。 - MortimerCat

Answer 6

这个答案是对最高票答案的小补充，与SQL Server相关。问题要求自动增量主键，当前答案确实添加了主键，但没有标记为自动增量。下面的脚本检查列是否存在，并启用自动增量标志来添加它。

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTable' AND COLUMN_NAME = 'PKColumnName')
BEGIN


ALTER TABLE dbo.YourTable
   ADD PKColumnName INT IDENTITY(1,1)

CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED

END

GO

- J. Minjire

---

很遗憾，这篇帖子有很多赞，你的回答完全符合我的需求，但愿我能再点赞。 - Sarz

Answer 7

当我们在现有表中添加一个标识列时，它会自动填充，无需手动填充。

- user3279092

Answer 8

通过设计，您可以设置标识 (1,1)。右键单击 tbl => 设计 => 在部分左侧 (右键单击) => 属性 => 在标识列中选择 #列。

属性

标识列

- gustavo herrera

---

你是否有关于这种方法是否可行的信息？OP正在询问是否这是“正确的处理方式”。更完整的答案可以帮助他们了解该方法的优缺点。 - jinglesthula

实际上我在开发环境中使用了这个选项，如果你想将此更改传递到生产环境，你应该通过查看依赖关系来验证标识字段是否被某些存储过程或触发器使用。 - gustavo herrera

Answer 9

如果您的表使用其主键或外键与其他表存在关系，则可能无法更改您的表。因此，您需要删除并重新创建该表。
要解决这些问题，您需要通过右键单击数据库并在高级选项中将数据类型设置为脚本以方案和数据的方式生成脚本。然后，使用此脚本更改您的列以识别并运行其查询以重新生成表。
您的查询将如下所示：

USE [Db_YourDbName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Drop TABLE [dbo].[Tbl_TourTable]

CREATE TABLE [dbo].[Tbl_TourTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Family] [nvarchar](150) NULL)  

GO

SET IDENTITY_INSERT [dbo].[Tbl_TourTable] ON 

INSERT [dbo].[Tbl_TourTable] ([ID], [Name], [Family]) VALUES (1,'name 1', 'family 1')
INSERT [dbo].[Tbl_TourTable] ([ID], [Name], [Family]) VALUES (1,'name 1', 'family 1')
INSERT [dbo].[Tbl_TourTable] ([ID], [Name], [Family]) VALUES (1,'name 1', 'family 1')
INSERT [dbo].[Tbl_TourTable] ([ID], [Name], [Family]) VALUES (1,'name 1', 'family 1')
INSERT [dbo].[Tbl_TourTable] ([ID], [Name], [Family]) VALUES (1,'name 1', 'family 1')
INSERT [dbo].[Tbl_TourTable] ([ID], [Name], [Family]) VALUES (1,'name 1', 'family 1')
INSERT [dbo].[Tbl_TourTable] ([ID], [Name], [Family]) VALUES (1,'name 1', 'family 1')

SET IDENTITY_INSERT [dbo].[Tbl_TourTable] off 

- Hamid

Answer 10

创建一个新的表格，名称不同但是列、主键和外键关联相同，在你的插入代码语句中将其链接。

例如：对于EMPLOYEE，请替换为EMPLOYEES。

CREATE TABLE EMPLOYEES(

    EmpId        INT NOT NULL IDENTITY(1,1), 
    F_Name       VARCHAR(20) ,
    L_Name       VARCHAR(20) ,
    DOB          DATE ,
    DOJ          DATE ,
    PRIMARY KEY (EmpId),
    DeptId int FOREIGN KEY REFERENCES DEPARTMENT(DeptId),
    DesgId int FOREIGN KEY REFERENCES DESIGNATION(DesgId),
    AddId int FOREIGN KEY REFERENCES ADDRESS(AddId)   
) 

然而，您必须删除现有的 EMPLOYEE 表或根据您的要求进行一些调整。

- kumarP