如何在SQL Server中为创建的表设置身份列

Question

我在 SQL Server 中创建了一个表，就像这样：

CREATE TABLE [UserName]
(
   [ID] [int] NOT NULL ,
   [Name] [nvarchar] (50) NOT NULL ,
   [Address] [nvarchar] (200) NULL

   CONSTRAINT [PK_UserName] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]
GO

如果我想将ID列设置为自增列，我需要做什么？我需要删除并重新创建这个表，并将ID列设置为[ID] [int] IDENTITY(1,1) NOT NULL吗？

使用删除和重新创建的方法，UserName表中的所有数据都将丢失。

还有其他方法可以将IDENTITY COLUMN设置为已创建的表的列而不会丢失数据吗？

我使用的是SQL Server 2008 R2 :)

- zey

---

你可以只删除列而不是整个表。但你不能向现有的列添加IDENTITY规范 - 这是不可能的。 - marc_s

@marc_s：嗯，那很有道理。但是如果我的表的ID列已经被用作其他地方的FK了，有没有更简单的方法来做到这一点呢？ - karan k

@karank：很抱歉，您不能向现有列添加“IDENTITY”规范。 - marc_s

@marc_s 你错了。将鼠标悬停在表格上，右键单击 -> 设计。选择你想要的列，在列属性中滚动到“标识规范”，将“否”更改为“是”。 - M H

@MH：是的 - 而且你知道这会发生什么吗？它会删除现有的表，然后使用新的标识规范重新创建表。您无法将标识规范添加到现有表、现有列。就是这样。 - marc_s

Answer 1

ALTER TABLE [UserName] DROP COLUMN [ID];

ALTER TABLE [UserName] 
    ADD [ID] integer identity not null;

- user330315

---

这将创建该列，但所有数据仍将丢失。 - Phil

@Phil：不是“所有数据”，只有现有ID列的值需要更新。如果该表被其他表引用，这可能会成为问题。 - user330315

我指的是该列中的“全部数据”- 但感谢您澄清。 - Phil

@phil：我认为zey提到“不丢失数据”时没有指现有的ID值，只是指现有的用户名和地址。 - user330315

Answer 2

尝试这个 -

DECLARE @temp TABLE
(
      ID INT NOT NULL 
    , Name NVARCHAR(50) NOT NULL 
    , [Address] NVARCHAR(200) NULL
) 

INSERT INTO @temp (ID, Name, [Address])
SELECT ID, Name, [Address]
FROM dbo.UserName

DROP TABLE dbo.UserName

CREATE TABLE dbo.UserName
(
      [ID] [int] IDENTITY(1,1) NOT NULL 
    , [Name] [nvarchar] (50) NOT NULL 
    , [Address] [nvarchar] (200) NULL
CONSTRAINT [PK_UserName] PRIMARY KEY CLUSTERED 
([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT dbo.UserName ON

INSERT INTO dbo.UserName (ID, Name, [Address])
SELECT ID, Name, [Address] 
FROM @temp

SET IDENTITY_INSERT dbo.UserName OFF

- Devart

---

使用 temp 表，这个不错！ - zey

Answer 3

最简单的方法是：

1. 在对象资源管理器中右键单击表格并选择“设计”

2. 选择要设置自增的列，并进入“列属性”

3. 在“标识规范”下，将“(是否标识)”更改为“Yes”

4. 点击 保存.... 完成 :)

   

希望这能帮到您。

- Sudeep A R

Answer 4

这里有一个最小记录的解决方案。

SELECT 
    IDENTITY(INT, 1,1) AS ID,
    Name, [Address]
INTO dbo.UserName_temp
FROM dbo.UserName;


ALTER TABLE dbo.UserName_temp
    ADD CONSTRAINT [PK_UserName] PRIMARY KEY CLUSTERED;

DROP TABLE dbo.UserName;

EXEC sp_rename 'dbo.UserName_temp', 'UserName';

SRC: http://sqlmag.com/sql-server/appending-identity-column-temporary-table

但是IDENTITY()函数只能在带有INTO表子句的SELECT语句中使用。

http://msdn.microsoft.com/en-us/library/ms189838.aspx

- an phu