How to create an auto-increment field in SQLite?

A simple answer: a field declared as integer primary key will be automatically added.

Here is a detailed answer: Starting from SQLite 2.3.4, if you declare a field in a table as integer primary key, no matter when you insert a null value to the field in the table, the null value will be automatically replaced with an integer greater than the maximum value of 1 for all rows of the field in the table; if the table is empty, it is replaced with 1. For example, suppose you have a data table like this:

Create Table T1 (a integer primary key, B integer );

In this data table, declare

Insert into T1 valuees (null, 123 );

Logically, it is equivalent:

Insert into T1 values (select max (a) from T1) + 1,123 );

A new API function sqlite3_last_insert_rowid () returns the integer key of 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 the key value that was previously deleted from the table. To always obtain the unique key in the entire table, add the keyword autoincrement before the declaration of the integer primary key. In this way, the selected key will always be 1 larger than the existing maximum key in the table. If the maximum key already exists in the table, the insert operation fails and returns a sqlite_full error code.