SQLite data table primary key setting ID auto-increment method

SQLite data table primary key setting ID auto-increment methodTags: sqliteintegerinsertnulltableapi2010-01-12 08:39 35135 People read Comments (8) favorite reports Classification:SQL (one)

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Fixed a long-troubled problem, the original SQLite in the primary key can also be set to self-increment:) method is declared as an INTEGER PRIMARY key field can automatically increase.
According to the information on the Internet, from the 2.3.4 version of SQLite, if you declare a field in a table as an INTEGER PRIMARY KEY, you simply insert a null value into that field of the table, and the null value is automatically replaced by a value larger than the maximum of all rows in that field in the table 1 If the table is empty, it will be replaced with 1.

CREATE TABLE "Processlist" (
[Id] Integer (4) PRIMARY KEY
, [Type] varchar (20)
, [Name] varchar (30)
, [isuse] int

)
Perform
INSERT INTO Processlist
Values
(NULL, ' A ', ' B ', 1)
In a logical sense equivalent to:
INSERT INTO Processlist VALUES ((SELECT max (Id) from processlist) +1, ' A ', ' B ', 1);

INSERT INTO Processlist
Values
(null, ' AA ', ' BB ', 1)
Execute two INSERT statements before querying the data in the table:
SELECT * FROM Processlist
The results are as follows:

Id Type Name Isuse
1 A B 1
2 AA BB 1

A new API function, Sqlite3_last_insert_rowid (), returns the Shaping key for the most recent insert operation. Note that this integer key is always 1 larger than the last key in the previous insert 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.

SQLite data table primary key setting ID auto-increment method