会发生什么自动增量主键删除后？ [英] What happens to autoincrement primary keys after delete?

问题描述

我试图创建对象和子对象的数据库文件。例如，

I'm trying to create a database file with objects and sub-objects. For example,

第一个表将罐存储的变化。每个瓶子条目将有关于罐子自动增量主ID键和数据，如高度，直径或什么的。

The first table would be of jars for storing change. Each jar entry would have a autoincrement primary ID key and data about the jar, like height, diameter or whatever.

第二个表将包含硬币。各硬币将有一个ID（根据需要）并也将具有，这被包含在和它的数据（例如值，一年等）的罐子的ID。

The second table would contain the coins. Each coin would have an ID (needed?) and would also have the ID of the jar that it is contained in and it's data (like value, year, etc.).

我的问题是，当一个被删除会发生什么变化自动增量键？说你有4罐：

My question is what happens to the autoincrement keys when one is deleted? Say you had 4 jars:

1
2
3
4

1 2 3 4

和你删号2.请问剩下的罐子重新编号123？还是会有差距？如果有一定的差距，是它填补由此造成将有2的ID的下一个罐子？或将下一个还是5？

and you deleted number 2. Would the remaining jars be renumbered 123? or would there be a gap? If there is a gap, is it filled so the next jar created would have an ID of 2? or would the next one still be 5?

感谢

推荐答案

您可以在sqlite的阅读 AUTOINCREMENT 文档：

You can read about that in the sqlite AUTOINCREMENT docs:

通常的算法是，得到
  新创建的行中的ROWID的一个
  比最大的ROWID较大
  表之前插入。如果
  表最初是空的，那么ROWID
  的1被使用。如果最大ROWID是
  等于最大可能的整数
  （9223372036854775807），则
  数据库引擎开始采摘
  随机阳性候选的ROWID
  直到找到一个是不
  previously使用。如果没有未使用ROWID
  可以在合理的数量后发现
  尝试，插入操作
  失败与错误SQLITE_FULL

The usual algorithm is to give the newly created row a ROWID that is one larger than the largest ROWID in the table prior to the insert. If the table is initially empty, then a ROWID of 1 is used. If the largest ROWID is equal to the largest possible integer (9223372036854775807) then the database engine starts picking positive candidate ROWIDs at random until it finds one that is not previously used. If no unused ROWID can be found after a reasonable number of attempts, the insert operation fails with an SQLITE_FULL error.

在总之，插入4行，删除第二，然后插入在端另一个之后，这些ID将是：

In short, the after inserting 4 rows, deleting the second, and then inserting another one on the end, the ids would be:

1 3 4 5

这篇关于会发生什么自动增量主键删除后？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！