我需要将一个表的主键更改为一个标识列,并且在表中已经有许多行。
我有一个脚本来清理id,以确保它们从1开始是顺序的,在我的测试数据库上运行良好。
更改列以具有标识属性的SQL命令是什么?
我需要将一个表的主键更改为一个标识列,并且在表中已经有许多行。
我有一个脚本来清理id,以确保它们从1开始是顺序的,在我的测试数据库上运行良好。
更改列以具有标识属性的SQL命令是什么?
当前回答
您不能更改现有列的标识。
你有两个选择,
创建一个带有标识的新表并删除现有表 创建一个具有标识的新列并删除现有列
方法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列。您需要做的是创建一个新列,从一开始就定义为IDENTITY,然后删除旧列,并将新列重命名为旧名称。
ALTER TABLE (yourTable) ADD NewColumn INT IDENTITY(1,1)
ALTER TABLE (yourTable) DROP COLUMN OldColumnName
EXEC sp_rename 'yourTable.NewColumn', 'OldColumnName', 'COLUMN'
Marc
我不相信您可以使用tsql将现有列更改为标识列。但是,您可以通过Enterprise Manager设计视图来完成。
或者,您可以创建一个新行作为标识列,删除旧列,然后重命名新列。
ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
NOT NULL
PRIMARY KEY CLUSTERED
您不能这样做,您需要添加另一列,删除原来的列并重命名新列,或者创建一个新表,复制数据并删除旧表,然后将新表重命名为旧表
如果你使用SSMS并在设计器中将标识属性设置为ON,下面就是SQL Server在幕后所做的事情。因此,如果你有一个名为[user]的表,如果你设置UserID和identity,就会发生这样的情况
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
GO
CREATE TABLE dbo.Tmp_User
(
UserID int NOT NULL IDENTITY (1, 1),
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
MiddleInitial char(1) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_User ON
GO
IF EXISTS(SELECT * FROM dbo.[User])
EXEC('INSERT INTO dbo.Tmp_User (UserID, LastName, FirstName, MiddleInitial)
SELECT UserID, LastName, FirstName, MiddleInitialFROM dbo.[User] TABLOCKX')
GO
SET IDENTITY_INSERT dbo.Tmp_User OFF
GO
GO
DROP TABLE dbo.[User]
GO
EXECUTE sp_rename N'dbo.Tmp_User', N'User', 'OBJECT'
GO
ALTER TABLE dbo.[User] ADD CONSTRAINT
PK_User PRIMARY KEY CLUSTERED
(
UserID
) ON [PRIMARY]
GO
COMMIT
已经说过,有一种方法可以通过设置位值来破解系统表,但这是不支持的,我不会这么做
根据我目前的情况,我采用这种方法。我想通过脚本插入数据后给一个主表的身份。
因为我想要追加身份,所以它总是从1开始到我想要的记录计数的结束。
--first drop column and add with identity
ALTER TABLE dbo.tblProductPriceList drop column ID
ALTER TABLE dbo.tblProductPriceList add ID INT IDENTITY(1,1)
--then add primary key to that column (exist option you can ignore)
IF NOT EXISTS (SELECT * FROM sys.key_constraints WHERE object_id = OBJECT_ID(N'[dbo].[PK_tblProductPriceList]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductPriceList]'))
ALTER TABLE [tblProductPriceList] ADD PRIMARY KEY (id)
GO
这将创建具有identity的相同主键列
我使用了这个链接:https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/
向现有表添加主键
如果最初的海报实际上想要设置一个现有的列为表的PRIMARY KEY,实际上不需要列为IDENTITY列(两种不同的东西),那么这可以通过t-SQL完成:
ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])
请注意,在PRIMARY KEY选项之后,列名周围的括号。
虽然这篇文章是旧的,我只是对请求者的需求做了一个假设,但我觉得这些额外的信息可能会对遇到这个线程的用户有所帮助,因为我相信对话可能会导致一个人认为,如果不先将现有的列添加为新列,就不能将其设置为主键,这是不正确的。