向现有列添加标识

2021-11-27 00:00:00 sql sql-server alter-table identity-column

我需要将表的主键更改为标识列，并且表中已经有许多行.

I need to change the primary key of a table to an identity column, and there's already a number of rows in table.

我有一个脚本来清理 ID，以确保它们从 1 开始是连续的，在我的测试数据库上运行良好.

I've got a script to clean up the IDs to ensure they're sequential starting at 1, works fine on my test database.

更改列以具有标识属性的 SQL 命令是什么?

What's the SQL command to alter the column to have an identity property?

推荐答案

您不能更改现有列的标识.

You can't alter the existing columns for identity.

你有两个选择，

  1. 创建一个具有身份的新表 &删除现有表

  1. Create a new table with identity & drop the existing table

创建一个带有标识 & 的新列删除现有列

Create a new column with identity & drop the existing column

方法 1.(新表)在这里您可以保留新创建的标识列上的现有数据值.请注意，如果不满足if not exists"，您将丢失所有数据，因此请确保您也将条件置于 drop 上！

Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you will lose all data if 'if not exists' is not satisfied, so make sure you put the condition on the drop as well!

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'

方法二(New column)你不能在新创建的标识列上保留现有的数据值，标识列将保存数字的序列.

Approach 2 (New column) You can’t retain the existing data values on the newly created identity column, The identity column will hold the sequence of number.

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 论坛帖子:

See the following Microsoft SQL Server Forum post for more details:

如何更改列以身份(1,1)

相关文章