当你的主键溢出时你会怎么做？
我们有一个带有自动增量 int 主键的表，其最大值现在达到了 T-SQL int 类型的限制。当我们尝试重新设置表的种子时（因为键中有很大的间隙，远没有足够的行作为最大 int 值），它以某种方式不断重置为最大 int< /代码> 值。
显然，这会导致严重的问题。 PK 永远不应该达到这个值，并且更改数据类型将是一项艰巨的任务。重新播种应该足够了，但是不起作用！
怎么会一直重置呢？
编辑：为了澄清情况，此查询SELECT MIN(CategoryID), MAX(CategoryID) FROM dbo.tblCategories返回-2147483647, 2147483647...这意味着在最小值和最大值处有实际的PK值int 类型。
We have a table, with an auto-increment int primary key, whose max value is now at the limit for the T-SQL int type. When we try to re-seed the table (because there are large gaps in the keys, nowhere near enough rows as the max int value), it somehow keeps getting reset to the max int value.
Obviously, this causes serious problems. The PK never should have gotten to this value and changing the data type would be a big task. Re-seeding should be sufficient, but it's not working!
How could it keep getting reset?
Edit: to clarify the situation, this query SELECT MIN(CategoryID), MAX(CategoryID) FROM dbo.tblCategories returns -2147483647, 2147483647... meaning that there are actual PK values at the min and max of the int type.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您可以“重新播种”表，以便下一个分配的标识列将小于表中当前的最大值。但是，任何后续的
DBCC CHECKIDENT都会将内部计数器重置回列中当前的最大值。也许这就是重置的来源？当然，插入最终会达到重复值，从而为制作支持人员带来有趣的时光。总的来说，你有麻烦了。我建议编写一个一次性脚本来删除/重置超高 ID 值。更新行（以及所有相关的外键值）是一种选择，尽管它涉及必须禁用外键约束，而且我不知道其他所有内容，所以我不会推荐它。另一种方法是使用更实用的 ID 值创建“高 ID”项目的所有数据的精确副本，然后删除原始条目。这是一个 hack，但它会产生一个更易于维护的数据库。
哦，并追踪那些输入这些高 ID 值的人，并且至少撤销他们对数据库的访问权限。
You can "reseed" a table so that the next-assigned identity column will be less than the current max value in the table. However, any subsquent
DBCC CHECKIDENTwill reset the internal counter back to the max value currently in the column. Perhaps that's where the reset is coming from? And of course an insert will eventually hit a duplicate value, resulting in Interesting Times for the production support crew.By and large, you're in trouble. I recommend working up a one-time script to remove/reset the uber-high ID values. Updating the rows (and all related foreign key values) is one option, though it would involve having to disable the foreign key constraints and I don't know what all else, so I wouldn't recommend it. The other would be to create exact copies of all the data for the "high-id" items using more pragmatic Id values, and then delete the original entries. This is a hack, but it would result in a much more maintainable database.
Oh, and track down the folks who put those high-id values in, and--at the very least--revoke their access rights to the database.
我参与的一个项目也有类似的问题 - 尽管在我们的例子中，有人选择了一个 4 位数字的字段作为主键，当我们接近 9999 条记录时，这有点不起作用......
在我们的例子中，我们在表上创建了一个新列并填充该列，将主键更改为该列，并将所有外键关系重新指向新键。
非常混乱，但确实有效。
I was involved with a project that had a similar issue - though in our case, someone had chosen a field with a 4-digit number as the primary key, which kinda didn't work as we approached 9999 records...
In our case, we created a new column on the table and populated that, changed the primary key to that column, and repointed all our foreign key relationships to the new key.
Very messy, but it did work.
创建一个具有相同结构的新表。从旧到新读（PK除外）。然后删除旧的并重命名新的。
Create a new table with same structure. Read from old to new (except for PK). Then delete the old and rename the new.