Self-increment primary key in SQLite3

SQLite empties the table and adds the self-increment column to the zero SQL standard with the TRUNCATE TABLE statement, which clears all the contents of the table. However, SQLite does not support this statement. Direct use in SQLiteDELETE  fromTableName can do it. For most DBMS, using delete is not as fast as truncate, because TRUNCATE does not have to access the entire table without logging data changes. Although SQLite does not support truncate, it is optimized for delete: Usually, when the table is emptied, the self-increment column must be zeroed. Here's how to define a self-increment column in SQLite:CREATE TABLETableName (IDINTEGER PRIMARY KEYAutoIncrement, Namevarchar, typeInteger), a table named Sqlite_sequence is automatically created when the SQLite database contains a self-increment column. This table consists of two columns: name and seq. The name records the table where the increment column is located, and the SEQ record is the current ordinal (the number of the next record is the current ordinal plus 1). If you want to set the ordinal of a self-increment column to zero, you only need to modify the Sqlite_sequence table. UPDATESqlite_sequenceSETSeq= 0 WHEREName='TableName'or you can delete the record directly:DELETE  fromSqlite_sequenceWHEREName='TableName'To zero the self-increment columns of all tables, empty the Sqlite_sequence table directly:DELETE  fromsqlite_sequence; SQLite in integerPRIMARY KEYthe use of AutoIncrement and ROWID in the design of the table with SQLite, suddenly think of a problem, is that I designed the table, each table has its own shaping ID value as the primary key, can not specify such an ID value, SQLite internally will be a rowid for each table, this rowid can be used as an implicit field, why not directly use the internal ROWID as the ID of each table primary key. 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 asINTEGER PRIMARY KEYAutoIncrement 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+1of 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. The new value of ROWID will randomly find a value that has not been used before this maximum number. Therefore, the value of ROWID is strictly monotonically increased before the ROWID reaches its maximum value. INTEGER PRIMARY KEYthe algorithm for the AutoIncrement self-growth 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:Create TableMeta_struct (IDINTEGER PRIMARY KEYAutoIncrement, Namevarchar, typeInteger); Then, define a first-level table that describes the structure of the other tables:Create TableMeta_table (TableIDINTEGER, Table_fieldintegerFinally, our application can generate a level two table that is actually used based on this level table. This ensures that the IDs in the compatibility meta_struct must be unique, and if a field is deleted, the ID value of the field cannot be reused, otherwise the first and the level two tables will be confused when the database is merged. So the primary key in the Meta_struct table can only use the self-growing field, not the ROWID. 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. 

Self-increment primary key in SQLite3