SQLite3 auto-incrementing primary key knowledge

I. SQLite clears the table and returns the auto-incrementing column to zero.

The SQL standard contains the TRUNCATE TABLE statement, which is used to clear all contents of the TABLE.
SQLite does not support this statement. You can directly use delete from TableName in SQLite.
For most DBMS, using DELETE is not as fast as using TRUNCATE, because TRUNCATE does not need to access the entire table, and does not need to record data changes.

Although SQLite does not support TRUNCATE, It optimizes DELETE:
When clearing a table, you also need to reset the auto-incrementing column to zero. The method for defining the auto-increment column in SQLite is as follows:
Copy codeThe Code is as follows: create table TableName (id integer primary key autoincrement, name varchar, type Integer );

When the SQLite database contains an auto-increment column, a table named sqlite_sequence is automatically created.
This table contains two columns: name and seq. Name records the table where the auto-increment column is located, and the sequence number of the seq record is the current sequence number plus 1 ).
To return the sequence number of an auto-increment column to zero, you only need to modify the sqlite_sequence table.
Copy codeThe Code is as follows: UPDATE sqlite_sequence SET seq = 0 WHERE name = 'tablename ';

You can also delete the record directly:
Copy codeThe Code is as follows: delete from sqlite_sequence WHERE name = 'tablename ';
To restore the auto-increment columns of all tables to zero, clear the sqlite_sequence table directly:
Copy codeThe Code is as follows: delete from sqlite_sequence;

Ii. Use of integer primary key autoincrement and rowid in Sqlite

When designing a table with sqlite, I suddenly thought of a problem, that is, in the table I designed, each table has its own integer id value as the primary key,
In fact, you can leave such an id value Unspecified. sqlite will add a rowid to each table. This rowid can be used as an implicit field,

Why not use the internal rowid as the primary key of each table's id directly.
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 can automatically set the value of this field to NULL,
The engine will be set to the largest rowid + 1. Of course, you can also set a non-NULL number to specify this value, but you must be careful not to cause conflicts.

The new value of rowid will randomly find an unused value before the maximum number. Therefore, before rowid reaches the maximum value, the value of rowid increases monotonically.
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 self-increasing field will throw a SQLITE_FULL error.
Second, when the auto-increment field adds a new value, it is to find 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:
Copy codeThe Code is as follows: 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:
Copy codeThe Code is as follows: 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 using the auto-increment field to record the used maximum value of the auto-increment field of each table. You can see that,
You can also use Update, Delete, and Insert operations, but it is not recommended to use these operations, which will confuse the engine. If rowid is used, there will also be such an internal table. You can maintain the rowid value, but you cannot see it.