SQLite self-growth Column

Declared

Integer primary key


Is considered to be self-increasing

When designing a table with SQLite, I suddenly thought of a problem, that is, in the table I designed, each table has
Your own integer id value as the primary key. In fact, you can not specify such an id value. SQLite will add a rowid to each table. This rowid can be considered as an implicit
Field is used, but maintained by the SQLite engine. Before 3.0, rowid is a 32-bit integer and after 3.0 is a 64-bit integer. Why not use this internal rowid directly?
For the id Primary Key of each table.

Immediately look for the SQLite document to see the difference between specifying integer primary key autoincrement and rowid without specifying auto-increment fields. Related documents are listed here:
Http://www.sqlite.org/autoinc.html

Http://www.sqlite.org/faq.html

Using an auto-increment field as the primary key has many problems, such as maintenance or primary key conflict resolution in large distributed applications. In some large distributed applications, GUID is generally used for primary keys, which can effectively avoid primary key conflicts and reduce the project for primary key maintenance. Of course, for small and medium-sized applications, the advantages of Self-increasing fields are more simple and fast.

In SQLite, an auto-increment field is defined as integer primary key autoincrement.
When inserting a new data, the engine automatically sets the value of this field to null, and the engine sets it to the maximum rowid + 1. Of course, you can also set it to a non-
Null number to specify this value, but you must be careful not to cause conflicts. When the value of rowid is greater than the maximum value that can be expressed
9223372036854775807
(Maximum value of rowid in versions 3.0 and later), the new value of rowid will randomly find a unused value before the maximum value. So before rowid reaches the maximum value, the value of rowid
Is strictly monotonic increased.

The algorithm of the integer primary key autoincrement auto-increment field is slightly different from that of rowid.

First
When the maximum value is reached, rowid will find the rowid corresponding to the Deleted field as the new value, and the auto-increment field will throw a sqlite_full error.

Second
When the auto-increment field adds a new value, it finds a rowid that has never been used as the new value, while rowid is to find the largest existing rowid + 1. In this case, the impact on the application will be relatively large, especially some meta records that depend on the ID value. It is only applicable to auto-increment fields but not rowid.
For example, we design a metadata record table:

Create Table meta_struct (ID integer primary key autoincrement, name varchar, type Integer );

Then, define a level-1 table to describe the structure of other tables:

Create Table meta_table (tableid integer, table_field integer)

Finally, our application can generate the actually used Level 2 table based on this level 1 table.
To ensure compatibility, the IDs in meta_struct must be unique. If a field is deleted, the ID value of this field cannot be reused. Otherwise, when the database is merged, first-level tables and second-level tables are messy. Therefore, the primary key in the meta_struct table can only use auto-increment fields, but not rowid.

Third
The engine automatically generates a sqlite_sequence table to record
You can see the maximum value and use the update, delete, and insert operations. However, we do not recommend this operation, which can confuse the engine. If rowid is used
With such an internal table, you can maintain the rowid value but cannot see it.

In this case, if you directly use rowid to replace the auto-increment field, you need to pay attention to whether it conflicts with your own application based on the slight differences between the two. If there is no conflict, it will be faster to use rowid.