如何自动重新计算主键值? [英] How a primary key value may be recalculated automatically?
问题描述
大家好。
有一部分代码使用T-sql,非常简单,不需要一些额外的解释。
选择 * 来自 MyTable
delete 来自 MyTable 其中 id = 2
只是一个简单的问题。我是一个更新的数据库领域...如果主键是整数类型,如何自动重新计算?
对于这么简单的问题很抱歉。
您至少有三个选项:
- 您可以使用 IDENTITY [ ^ ]
- 使用uniqueidentifier和 NEWID [ ^ ]
- 或使用 SEQUENCE [ ^ ]
我个人更喜欢NEWID over IDENTITY特别是如果键需要在diff中是唯一的erent数据库。
嗨Dzianis,
根据我对你的问题的理解,你想重新计算/重置身份值列,而不是主键本身。
如果我是正确的,请使用下面的DBCC命令。这将帮助您重置列的标识值。
DBCC CHECKIDENT(' [table_name]',RESEED,[new_reseed_value])
所以,在你的情况下,你可以把下面的东西
DBCC CHECKIDENT(' MyTable',RESEED, 1 )
以上查询将重置您的标识列为了价值1.所以从下一个值2将插入列id。
如果您对此有任何疑虑或疑问,请与我们联系。 />
谢谢
我的答案是不要这样做。
为什么?
只是因为在主键值上有一个漏洞没有任何缺点,并且重新编号为ke y不提供任何收益(空间或速度)。
但重新编号键有一个缺点,你必须在重新编号时关闭对基站的所有访问。
Hello to everyone
There is a part of code using T-sql, quite simple and it does not need some extra explanations.
select * from MyTable
delete from MyTable where id = 2
Just one simple question. I am a newer is the DB sphere... How can a primary key be recounted automatically if it is of integer type?
Sorry for such a simple question.
You have at least three options:
- you can use IDENTITY[^]
- use uniqueidentifier along with NEWID[^]
- or use a SEQUENCE[^]
Personally I'd prefer NEWID over IDENTITY especially if the keys need to be unique across different databases.
Hi Dzianis,
As per my understanding of your question, you want to recalculate/ reset identity value of the column, not the primary key itself.
If I am correct then please use below DBCC command. That will help you to reset the identity value of the column.
DBCC CHECKIDENT('[table_name]', RESEED, [new_reseed_value])
So, in your case you can put something like below"
DBCC CHECKIDENT('MyTable', RESEED, 1)
Above query will reset your identity column to value 1. So from the next value 2 will be inserted for the column id.
Please let me know if you have any concern or query on this.
Thanks
My answer is Don't do it.
Why ?
Simply because there is no downside to have a hole in primary key values, and renumbering the key offer no gain (of space or speed).
But renumbering the key have a downside, you have to shutdown all access to the base while you are renumbering it.
这篇关于如何自动重新计算主键值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!