删除行后如何重置主键ID. [英] How can reset primary key id after deleted the row.

问题描述

朋友们，

我从现有表中删除了用身份方法创建的主键列的记录.删除记录后，当我将新记录输入到表中时，它的身份取自上次delete.next的下一个值.但是我想将它们从第一个值中重置.我该怎么办

在此先谢谢您

Hi friends,

I deleted the record from existing table which primary key column created with identity method. after deleting record when i enter the new record to table than it identity taken from the next value of last deleted.but i want to reset them from first value. how can i do it

Thanks in advance

推荐答案

DBCC CHECKIDENT(MyTable，RESEED，1)将重置表上的索引

DBCC CHECKIDENT (MyTable, RESEED, 1) will reset the index on your table

使用DBCC重新设置主键列值，我认为不应该这样做.代理键的一个特征是键值本身是无意义的，只是唯一的.这正是身份的作用.

使用代理密钥时，不会存在所有数字都存在的隔离(并且不应存在任何隔离).

例如对于如果您有100行，并且kay值是从1到100，请考虑删除记录78时会发生什么.现在序列中有一个空洞，但是如果将标识重新设置为78以获取缺少的值，则下一个插入将失败，因为记录79、80等已经存在.

While you can use DBCC to re-seed the primary key column value, I think this shouldn''t be done. One characteristics of a surrogate key is that the key value itself is meaningless, it''s just unique. This is exactly what identity does.

When a surrogate key is used, there is no quarantee that all the numbers are present (and there shouldn''t be no quarentee).

For example if you have 100 rows and the kay values are from 1 to 100, consider what happens when record 78 is deleted. Now there''s a hole in the sequence, but if you reseed the identity to 78 to get the missing value, the next inserts will fail since records 79, 80 and so on already exist.

嗨试试这个

hi try this

The following line resets the Identity value for the table to 0 so that the next record added starts at 1.

DBCC CHECKIDENT('Tablename', RESEED, 0)

问候
sarva

regards
sarva

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