给现有列添加身份标识

Question

给现有列添加身份标识

sqlsql-serveralter-tableidentity-column

560

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

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

如何使用SQL命令更改该列以具有标识属性？

- Kirschstein

21个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Raj · Answer 1

按照设计，没有简单的方法来为现有列打开或关闭标识属性。唯一的清洁方法是创建一个新列并使其成为标识列，或创建一个新表并迁移数据。

如果我们使用SQL Server Management Studio在列“id”上去掉标识值，将会创建一个新临时表，将数据移到临时表中，删除旧表并重命名新表。

使用管理工具对更改进行操作，然后在设计器中右键单击并选择“生成更改脚本”。

您将看到这就是SQL Server在后台执行的操作。

- A. Omalley · Answer 2

如果原帖作者实际上想要将现有列设置为表的PRIMARY KEY，并且实际上不需要该列成为IDENTITY列（两个不同的事情），那么可以通过t-SQL完成：

ALTER TABLE [YourTableName]
ADD CONSTRAINT [ColumnToSetAsPrimaryKey] PRIMARY KEY ([ColumnToSetAsPrimaryKey])

请注意在PRIMARY KEY选项后面的列名括号。

尽管这篇文章已经过时，我做了一个关于请求者需求的假设，但我认为这些额外的信息可能对那些遇到这个线程的用户有所帮助，因为我相信这个对话可能会让人们认为现有的列不能被设置为主键，而不是先将其添加为新列，这是不正确的。

- Enchantres · Answer 3

在我的情况下，有很多没有标识的表格，所以复制和重新创建将会花费太长时间。如果你正在使用SQL Server Management Studio，我找到了一种更简单的方法来做到这一点：

打开工具 -> 选项 -> 设计师 -> 表格和数据库设计师。
取消选中 "防止保存会导致表格重新创建的更改" 复选框，以便在更改表格设计时不会删除表格并重新创建。
保存更改。
右键点击表格，然后点击设计。
选择列，打开属性，并从标识下拉菜单中选择 "是"。

表格不会被删除，表格内的数据和依赖关系保持不变。

- Ken.Fukizi · Answer 4

修改列的标识属性：

在服务器资源管理器中，右键单击要修改标识属性的表，然后单击“打开表定义”。
该表将在表设计器中打开。
清除要更改的列的“允许为空”复选框。
在“列属性”选项卡中，展开“标识规范”属性。
单击“是否为标识”子属性的网格单元，并从下拉列表中选择“Yes”。
在“标识种子”单元格中输入一个值。此值将被分配给表中的第一行，默认情况下将分配值1。

就是这样，对我很有用。

- Vamsi Krishna Mopievi · Answer 5

在对象资源管理器中右键点击表名，会弹出一些选项。点击“设计”选项，将打开一个新的选项卡用于编辑此表。您可以在“列属性”中添加标识约束。请注意保留HTML标签。

- Jeremy Smyth · Answer 6

很遗憾，没有这个选项；IDENTITY属性属于表而不是列。

更简单的方法是在GUI中完成，但如果不行的话，可以绕路复制数据、删除列、重新添加带有identity的列并将数据放回去。

参见这里以获得详细说明。

- William Edmondson · Answer 7

我不相信你可以使用T-SQL修改现有列为标识列。但是，你可以通过Enterprise Manager设计视图来实现。

或者，你可以创建一个新的行作为标识列，删除旧列，然后重命名新列。

ALTER TABLE FooTable
ADD BarColumn INT IDENTITY(1, 1)
               NOT NULL
               PRIMARY KEY CLUSTERED

- Ajay2707 · Answer 8

根据我的当前情况，我遵循这种方法。我想在通过脚本插入数据后为主表赋予身份标识。由于我想追加身份标识，所以它总是从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

这将创建具有自增的相同主键列。

我使用了以下链接：https://blog.sqlauthority.com/2014/10/11/sql-server-add-auto-incremental-identity-column-to-table-after-creating-table/ 将主键添加到现有表。

- Kyle Gibbar · Answer 9

生成一个脚本，用于所有具有主键= bigint且未设置标识的表；这将返回每个表的生成脚本列表；

SET NOCOUNT ON;

declare @sql table(s varchar(max), id int identity)

DECLARE @table_name nvarchar(max),
        @table_schema nvarchar(max);

DECLARE vendor_cursor CURSOR FOR 
SELECT
  t.name, s.name
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS (
    SELECT
    [c].[name]
    from sys.columns [c]
    join sys.types [y] on [y].system_type_id = [c].system_type_id
    where [c].[object_id] = [t].[object_id] and [y].name = 'bigint' and [c].[column_id] = 1
) and NOT EXISTS 
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
) and exists (
    select 1 from sys.indexes as [i] 
    inner join sys.index_columns as [ic]  ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
    where object_name([ic].[object_id]) = [t].[name]
)
OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor 
INTO @table_name, @table_schema

WHILE @@FETCH_STATUS = 0
BEGIN

DELETE FROM @sql

declare @pkname varchar(100),
    @pkcol nvarchar(100)

SELECT  top 1
        @pkname = i.name,
        @pkcol = COL_NAME(ic.OBJECT_ID,ic.column_id)
FROM    sys.indexes AS [i]
INNER JOIN sys.index_columns AS [ic] ON  i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1 and OBJECT_NAME(ic.OBJECT_ID) = @table_name

declare @q nvarchar(max) = 'SELECT  '+@pkcol+' FROM ['+@table_schema+'].['+@table_name+'] ORDER BY '+@pkcol+' DESC'

DECLARE @ident_seed nvarchar(max) -- Change this to the datatype that you are after
SET @q = REPLACE(@q, 'SELECT', 'SELECT TOP 1 @output = ')
EXEC sp_executeSql @q, N'@output bigint OUTPUT', @ident_seed OUTPUT

insert into  @sql(s) values ('BEGIN TRANSACTION')
insert into  @sql(s) values ('BEGIN TRY')

-- create statement
insert into  @sql(s) values ('create table ['+@table_schema+'].[' + @table_name + '_Temp] (')

-- column list
insert into @sql(s) 
select 
    '  ['+[c].[name]+'] ' +
    y.name + 

    (case when [y].[name] like '%varchar' then
    coalesce('('+(case when ([c].[max_length] < 0 or [c].[max_length] >= 1024) then 'max' else cast([c].max_length as varchar) end)+')','')
    else '' end)

     + ' ' +
    case when [c].name = @pkcol then 'IDENTITY(' +COALESCE(@ident_seed, '1')+',1)' else '' end + ' ' +
    ( case when c.is_nullable = 0 then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT ('+(
        REPLACE(
            REPLACE(
                LTrim(
                    RTrim(
                        REPLACE(
                            REPLACE(
                                REPLACE(
                                    REPLACE(
                                        LTrim(
                                            RTrim(
                                                REPLACE(
                                                    REPLACE(
                                                        object_definition([c].default_object_id)
                                                    ,' ','~')
                                                ,')',' ')
                                            )
                                        )
                                    ,' ','*')
                                ,'~',' ')
                            ,' ','~')
                        ,'(',' ')
                    )
                )
            ,' ','*')
        ,'~',' ')
    ) +
    case when object_definition([c].default_object_id) like '%get%date%' then '()' else '' end
    +
    ')','') + ','
 from sys.columns c
 JOIN sys.types y ON y.system_type_id = c.system_type_id
  where OBJECT_NAME(c.[object_id]) = @table_name and [y].name != 'sysname'
 order by [c].column_id


 update @sql set s=left(s,len(s)-1) where id=@@identity

-- closing bracket
insert into @sql(s) values( ')' )

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] ON')

declare @cols nvarchar(max)
SELECT @cols = STUFF(
    (
        select ',['+c.name+']'
        from sys.columns c
        JOIN sys.types y ON y.system_type_id = c.system_type_id
        where c.[object_id] = OBJECT_ID(@table_name)
        and [y].name != 'sysname'
        and [y].name != 'timestamp'
        order by [c].column_id
        FOR XML PATH ('')
     )
    , 1, 1, '')

insert into @sql(s) values( 'IF EXISTS(SELECT * FROM ['+@table_schema+'].['+@table_name+'])')
insert into @sql(s) values( 'EXEC(''INSERT INTO ['+@table_schema+'].['+@table_name+'_Temp] ('+@cols+')')
insert into @sql(s) values( 'SELECT '+@cols+' FROM ['+@table_schema+'].['+@table_name+']'')')

insert into @sql(s) values( 'SET IDENTITY_INSERT ['+@table_schema+'].['+@table_name+'_Temp] OFF')


insert into @sql(s) values( 'DROP TABLE ['+@table_schema+'].['+@table_name+']')

insert into @sql(s) values( 'EXECUTE sp_rename N''['+@table_schema+'].['+@table_name+'_Temp]'', N'''+@table_name+''', ''OBJECT''')

if ( @pkname is not null ) begin
    insert into @sql(s) values('ALTER TABLE ['+@table_schema+'].['+@table_name+'] ADD CONSTRAINT ['+@pkname+'] PRIMARY KEY CLUSTERED (')
    insert into @sql(s)
        select '  ['+COLUMN_NAME+'] ASC,' from information_schema.key_column_usage
        where constraint_name = @pkname
        GROUP BY COLUMN_NAME, ordinal_position
        order by ordinal_position

    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end

insert into  @sql(s) values ('--Run your Statements')
insert into  @sql(s) values ('COMMIT TRANSACTION')
insert into  @sql(s) values ('END TRY')
insert into  @sql(s) values ('BEGIN CATCH')
insert into  @sql(s) values ('        ROLLBACK TRANSACTION')
insert into  @sql(s) values ('        DECLARE @Msg NVARCHAR(MAX)  ')
insert into  @sql(s) values ('        SELECT @Msg=ERROR_MESSAGE() ')
insert into  @sql(s) values ('        RAISERROR(''Error Occured: %s'', 20, 101,@msg) WITH LOG')
insert into  @sql(s) values ('END CATCH')

declare @fqry nvarchar(max)

-- result!
SELECT @fqry = (select char(10) + s from @sql order by id FOR XML PATH (''))


SELECT @table_name as [Table_Name], @fqry as [Generated_Query]
PRINT 'Table: '+@table_name
EXEC sp_executeSql @fqry

    FETCH NEXT FROM vendor_cursor 
    INTO @table_name, @table_schema
END 
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

- Shivalik Chakravarty · Answer 10

基本上有四个逻辑步骤。

创建一个新的标识列。为这个新列打开插入标识。
将源列（您希望转换为标识的列）中的数据插入到这个新列中。
关闭新列的插入标识。
删除源列并将新列重命名为源列的名称。

可能会有一些更复杂的问题，比如跨多个服务器工作等。

请参考以下文章以了解步骤（使用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