SQL是否将步数或行数添加到主键的最后一个条目中? [英] Does SQL add the number of steps or the number of rows added to the last entry of the primary key?

问题描述

我有一个带有ID，Value，Date，Cumulative，UniqueID列的数据表，其中，唯一ID是我的主键. UniqueID是一个身份密钥，它会随着每个新条目自动递增.我的问题是2个部分

a)有时数据会被多次插入，因此主键会采取步数或添加到表中的行数来增加标识列值(UniqueID)的问题.

b)由于数据删除，UniqueID中也可能存在间隙.我该如何检查表中是否存在间隙以及如何解决该间隙.是否必须删除表并在出现错误时再次重新启动差距???
进入此页面，但无法确定是在创建表时执行此操作还是在创建表后执行此操作.
http://msdn.microsoft.com/zh-cn/library/aa933196(v=sql.80).aspx

提前感谢
Ananth

Hi,


I have a datatable with the columns ID,Value,Date,Cumulative,UniqueID where Unique ID is my primary key. UniqueID is an Identity key which auto increments with every new entry.My question is 2 parts

a) Sometimes the data is inserted multiple times so does the primary key take the number of steps or the number of rows added to table to increment the Identity Column value (UniqueID) ???

b)Also there is a possibility of gap in the UniqueID because data deletion.How do I go about checking whether there is a gap in the table and how to resolve it.Do I have to delete the table and startover again when there is a gap???
Came across this page but could not figure out whether to do this when the table is created or can it be done after the table is created.
http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx

Thanx in advance
Ananth

推荐答案

唯一键完成了它应该做的事情:它保证其唯一性，因此可以安全地用于引用其他表中的对象.每种技术都基于对键值的任何假设(特别是基于键的连续值的假设)是错误的，绝不应该使用..

关于连续值的假设显然是错误的.假设您删除了几行，那么一些唯一值就消失了.您可能希望以后可以再次使用唯一ID的值并填补空白(但不能保证)，但是在添加任何行之前，该空白是不可避免的.依赖连续值的尝试将使您的数据模型不一致，而试图删除整个表以期以后恢复相同数据的尝试将破坏逻辑数据的完整性.您只想技术上"删除并重新创建一些行，但是稍后要逻辑上"还原对象，只是移动已还原对象的唯一ID.您将如何从其他表中还原那些对象的引用?即使可以做到，此操作也不是事务性的:删除某些行后，其他一些用户可能会添加一些；那你打算怎么办?不，它不起作用.您根本不应该使用基于任何键值假设的任何技术.

—SA

Unique key does what it should do: it guarantees its uniqueness, so it can be safely used for referencing of object from other tables. Every technique based an any assumption of the values of the key (and in particular, on the assumption of consecutive values of the key) is wrong and should never be used.

The assumption about the consecutive values would be apparently wrong. Assume you delete few rows, so some unique values disappears. You could possibly expect that the values for unique ID can be later used again and fill the gaps (but it cannot be guaranteed), but the gap is unavoidable before you add any more rows. An attempt to rely on consecutive values will make your data model inconsistent, and the attempt to delete the whole table in hope to restore the same data later will destroy logical data integrity. You want to remove and re-create some rows only "technically", but "logically" restore the objects later, just shifting unique IDs for the restored object. How are you going to restore references from other table for those objects? Even if you can do it, this operation is not transactional: after you remove some rows, some other user might add some; and what you going to do with that? No, it cannot work. You simply should not use any technique based on any assumption of the key values.

—SA

只需添加SAKryukov所说的内容，

按顺序递增而没有漏洞"的数字通常是业务需求，我在应用程序层而不是数据库层中实现它们.

这通常是通过重新编号文档编号"列来完成的.

Just to add to what SAKryukov said,

Sequentially incremented without "holes" numbers are usually a business requirement and I implement them in the application layer, and not in the database layer.

This is usually done by a renumbering a "document number" column.

这篇关于SQL是否将步数或行数添加到主键的最后一个条目中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！