我需要将一个表的主键更改为一个标识列,并且在表中已经有许多行。
我有一个脚本来清理id,以确保它们从1开始是顺序的,在我的测试数据库上运行良好。
更改列以具有标识属性的SQL命令是什么?
我需要将一个表的主键更改为一个标识列,并且在表中已经有许多行。
我有一个脚本来清理id,以确保它们从1开始是顺序的,在我的测试数据库上运行良好。
更改列以具有标识属性的SQL命令是什么?
当前回答
简单的解释
使用sp_RENAME重命名现有列
EXEC sp_RENAME 'Table_Name。Existing_ColumnName', 'New_ColumnName', 'COLUMN'
重命名示例:
现有列UserID被重命名为OldUserID
EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'
然后使用alter query添加一个新列来设置为主键和标识值
ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)
使用实例设置主键
新创建的列名为UserID
ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
然后删除重命名列
ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName
删除重命名列的示例
ALTER TABLE Users DROP COLUMN OldUserID
现在,我们向表上的现有列添加了一个主键和标识。
其他回答
这里有一个很酷的解决方案: 在列上添加或删除标识属性
简而言之,在SQL管理器中手动编辑您的表,切换标识,不要保存更改,只显示将为更改创建的脚本,复制它并稍后使用。
这节省了大量的时间,因为它(脚本)包含了所有与您更改的表相关的外键、索引等。手动写这个…上帝保佑。
简单的解释
使用sp_RENAME重命名现有列
EXEC sp_RENAME 'Table_Name。Existing_ColumnName', 'New_ColumnName', 'COLUMN'
重命名示例:
现有列UserID被重命名为OldUserID
EXEC sp_RENAME 'AdminUsers.UserID' , 'OldUserID', 'COLUMN'
然后使用alter query添加一个新列来设置为主键和标识值
ALTER TABLE TableName ADD Old_ColumnName INT NOT NULL PRIMARY KEY IDENTITY(1,1)
使用实例设置主键
新创建的列名为UserID
ALTER TABLE Users ADD UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
然后删除重命名列
ALTER TABLE Table_Name DROP COLUMN Renamed_ColumnName
删除重命名列的示例
ALTER TABLE Users DROP COLUMN OldUserID
现在,我们向表上的现有列添加了一个主键和标识。
根据设计,没有简单的方法来打开或关闭现有列的标识特性。要做到这一点,唯一干净的方法是创建一个新列并使其成为标识列,或者创建一个新表并迁移数据。
如果我们使用SQL Server Management Studio去除列“id”上的标识值,则会创建一个新的临时表,数据被移动到临时表中,旧表被删除,新表被重命名。
使用Management Studio进行更改,然后在设计器中右键单击并选择“生成更改脚本”。
你会看到这就是SQL server在后台所做的。
您不能更改现有列的标识。
你有两个选择,
创建一个带有标识的新表并删除现有表 创建一个具有标识的新列并删除现有列
方法1。(新表)在这里,您可以保留新创建的标识列上的现有数据值。请注意,如果不满足'if not exists',您将丢失所有数据,因此请确保您将条件也放在drop上!
CREATE TABLE dbo.Tmp_Names
(
Id int NOT NULL
IDENTITY(1, 1),
Name varchar(50) NULL
)
ON [PRIMARY]
go
SET IDENTITY_INSERT dbo.Tmp_Names ON
go
IF EXISTS ( SELECT *
FROM dbo.Names )
INSERT INTO dbo.Tmp_Names ( Id, Name )
SELECT Id,
Name
FROM dbo.Names TABLOCKX
go
SET IDENTITY_INSERT dbo.Tmp_Names OFF
go
DROP TABLE dbo.Names
go
Exec sp_rename 'Tmp_Names', 'Names'
方法2(新列)不能在新创建的标识列上保留现有数据值,标识列将保留数字序列。
Alter Table Names
Add Id_new Int Identity(1, 1)
Go
Alter Table Names Drop Column ID
Go
Exec sp_rename 'Names.Id_new', 'ID', 'Column'
请参阅以下Microsoft SQL Server论坛帖子了解更多细节:
如何将列更改为identity(1,1)
基本上有四个逻辑步骤。
创建一个新的Identity列。为这个新列打开Insert Identity。 将源列(希望转换为Identity的列)中的数据插入到这个新列中。 关闭新列的Insert Identity。 删除源列并将新列重命名为源列的名称。
可能会有一些更复杂的事情,比如跨多个服务器工作等。
有关步骤(使用ssms和T-sql),请参阅下面的文章。这些步骤适用于不太熟悉T-SQL的初学者。
http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx