Sqlite3 self-increment key setting (Create self-increment field)

When using SQLite to design a table, each table has its own shaping ID value as the primary key, you can not specify such an ID value, SQLite will have a rowid for each table, this rowid can be used as an implicit field, but maintained by the SQLite engine , before 3.0 rowID is a 32-bit integer, 3.0 is a 64-bit integer, why not directly use this internal ROWID as the ID of each table primary key it.

The relevant documentation is here:? http://www.sqlite.org/autoinc.html?http://www.sqlite.org/faq.html

What is the difference between using the specified integer PRIMARY KEY autoincrement and not specifying a self-growing field with ROWID:

There are many problems with using self-growing fields as primary keys, such as maintenance or key conflict resolution in large distribution applications. In some large-scale distribution applications, the primary key is generally chosen GUID, which can effectively avoid the primary key conflicts, reduce the primary key maintenance of the project. Of course, for small and medium-sized applications, the benefits of self-growth fields are more, simpler and faster.

In SQLite, a self-growing field is defined as an integer PRIMARY KEY autoincrement, so when inserting a new data, you simply specify the value of the field as null, and the engine automatically sets its value, and the engine is set to the maximum rowid+1. Of course, you can also set a number that is not NULL to specify this value yourself, but you must be careful not to cause conflicts. When the value of this rowid is greater than the maximum value that can be expressed in 9223372036854775807 (3.0 and later versions of rowID maximum), the new value of ROWID will randomly find a value that is not used before the maximum number. Therefore, the value of ROWID is strictly monotonically increased before the ROWID reaches its maximum value.
The algorithm for the INTEGER PRIMARY KEY autoincrement self-growing field is slightly different from the ROWID.
First, when the maximum value is reached, rowID will find the rowid of the deleted field as the new value, and the self-growing field will throw a sqlite_full error.
Second, since the growth field adds new values, it is looking for a rowid that has never been used as a new value, while rowID is looking for the largest existing rowid+1. The impact on the application will be large, especially some meta records that have a dependency on the ID value, and are only suitable for self-growing fields and cannot be used with ROWID.

For example, we design a meta-record table:
drop table test;
CREATE TABLE Test (
[Tkid] integer PRIMARY key AutoIncrement,--Set Primary key
[Tktype] int default 0,
[TableID] varchar (50),
[CreateDate] DateTime default (DateTime (' Now ', ' localtime '))--time
);

Third, using the Self-growth field, the engine automatically generates a Sqlite_sequence table that records the maximum value of the used values for each table's self-growing field, which the user can see and can use with Update, delete, and insert operations, but is not recommended. This can make the engine chaotic. If you use ROWID, there will also be an internal table that the user can maintain ROWID values but not see.
In this case, if you use ROWID directly instead of the self-increment field, depending on the subtle difference between the two, you need to be aware of whether it conflicts with your application, and if there is no conflict, then using ROWID will be faster.

Create a self-increment field in SQLite:

Simple answer: A field declared as an INTEGER PRIMARY KEY is automatically incremented.

Starting with the 2.3.4 version of SQLite, if you declare a field in a table to be an integer PRIMARY KEY, then whenever you insert a null value into that field of the table, the null value is automatically replaced by an integer that is 1 larger than the maximum value of all rows in the field in the table; If the table is empty, it will be replaced with 1.

A new API function, Sqlite3_last_insert_rowid (), returns the reshape key for the most recent insert operation.

Note that this integer key is always 1 larger than the last key in the previously inserted table. The new key is unique relative to the existing key in the table, but it may overlap with the key values that were previously removed from the table. To always get the key that is unique throughout the table, add the keyword AutoIncrement before the declaration of the integer PRIMARY key. The selected key will always be 1 larger than the largest key already present in the table. If the maximum possible key already exists in the table, the insert operation fails and returns a sqlite_full error code.

This article from "Casket Night Blog" blog, declined reprint!

Sqlite3 self-increment key setting (Create self-increment field)