如何在SQL Server中重置主键 [英] How to Reset Primary Key in Sql Server

问题描述

大家好，
我从表中删除了一些日期，例如id = 25和id = 27.现在我有id = 28处的数据.能否请您告诉我如何将主键从1重置为.......这样旧数据将不会删除，主键将显示为1至... ............

仅重置主键，它将用其他数据替换25和27

Hello All,
I have deleted some date from my table like at id=25 and id=27. Now i Have data at id=28.Can you please tell me how to reset primary key from 1 to ...........so that old data will not delete and primary key will show from 1 to ..............

only to reset primary key and it will replace 25 and 27 with the other data

推荐答案

谢谢您的提问.

从 YourTabe 中删除外键引用(如果有)，然后在Microsoft SQL Server Management Studio的列属性窗口中将Is Identity设置为 No .使用Management Studio执行以下SQL.

Thank you for your question.

Remove foreign key reference (if any) from YourTabe and set Is Identity to No in column properties windows in Microsoft SQL Server Management Studio. Execute the bellow SQL using Management Studio.

USE YourDataBase

SELECT [YourTableId],ROW_NUMBER() OVER (ORDER BY YourTableId ) AS RowNumber
    into #tempTable from [YourTable]

DECLARE your_table_cursor CURSOR FOR
    SELECT [YourTableId], RowNumber
    FROM #tempTable

OPEN your_table_cursor

DECLARE @YourTableId int
DECLARE @RowNumber int

FETCH NEXT FROM your_table_cursor
INTO @YourTableId, @RowNumber

WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE  [YourTable]
        SET [YourTableId] = @RowNumber
        WHERE [YourTableId] = @YourTableId

    FETCH NEXT FROM your_table_cursor
    INTO @YourTableId, @RowNumber
END


CLOSE your_table_cursor
DEALLOCATE your_table_cursor

DROP TABLE #tempTable

在Management Studio的列属性窗口中，将身份身份"设置为是，并将身份增量设置为1，将身份种子设置为1.然后设置参考.

谢谢，
Mamun

Set Is Identity to Yes and set Identity increment to 1 and Identity Seed to 1 in column properties windows in Management Studio. Then set reference.

Thanks,
Mamun

我认为您要问的第一个问题是:为什么?

I think the first question you''ll have to ask yorself is: Why???

我认为最简单的方法是复制表(即创建一个与表相同的新表，将所有帖子插入到新表中.这样，您应该获得一个不错的ID顺序)

另一种方法是打开的标识，手动更改ID，然后
再次打开ID.

尽管ID列中有孔也没关系.

I think the easiest way is to copy the table (ie make a new table that is identical to your table, insert all the posts to the new table. That way you should get a nice Id order )

Another way is to turn the identity of , manually change the ids, and then
turn the id on again.

It should not matter much though that an ID column has holes in it.

这篇关于如何在SQL Server中重置主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！