我需要将一个表的主键更改为一个标识列,并且在表中已经有许多行。
我有一个脚本来清理id,以确保它们从1开始是顺序的,在我的测试数据库上运行良好。
更改列以具有标识属性的SQL命令是什么?
我需要将一个表的主键更改为一个标识列,并且在表中已经有许多行。
我有一个脚本来清理id,以确保它们从1开始是顺序的,在我的测试数据库上运行良好。
更改列以具有标识属性的SQL命令是什么?
当前回答
考虑使用SEQUENCE而不是IDENTITY。
在sql server 2014(我不知道低版本),你可以简单地使用序列。
CREATE SEQUENCE sequence_name START WITH here_higher_number_than_max_existed_value_in_column INCREMENT BY 1;
ALTER TABLE table_name ADD CONSTRAINT constraint_name DEFAULT NEXT VALUE FOR sequence_name FOR column_name
从这里开始:将序列作为列的默认值
其他回答
不能将列更改为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
您不能这样做,您需要添加另一列,删除原来的列并重命名新列,或者创建一个新表,复制数据并删除旧表,然后将新表重命名为旧表
如果你使用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
已经说过,有一种方法可以通过设置位值来破解系统表,但这是不支持的,我不会这么做
修改列的标识属性:
In Server Explorer, right-click the table with identity properties you want to modify and click Open Table Definition. The table opens in Table Designer. Clear the Allow nulls check box for the column you want to change. In the Column Properties tab, expand the Identity Specification property. Click the grid cell for the Is Identity child property and choose Yes from the drop-down list. Type a value in the Identity Seed cell. This value will be assigned to the first row in the table. The value 1 will be assigned by default.
就是这样,对我很管用
如果你碰巧使用Visual Studio 2017+
在服务器对象资源管理器中,右键单击表格,选择“视图代码” 向列添加修饰符“IDENTITY” 更新
这将为你做的一切。
简单的解释
使用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
现在,我们向表上的现有列添加了一个主键和标识。