Keyword Autoincrement of SQLite (Auto increment) and sqliteincrement

Keyword Autoincrement of SQLite (Auto increment) and sqliteincrement

The AUTOINCREMENT of SQLite is a keyword used to automatically increase the field values in the table. We can use the AUTOINCREMENT keyword to automatically increase the field value when creating a table on a specific column name.

Note: The AUTOINCREMENT keyword can be used for integer fields.

Syntax

The basic usage of the AUTOINCREMENT keyword is as follows:

CREATE TABLE table_name(  column1 INTEGER AUTOINCREMENT,  column2 datatype,  column3 datatype,  .....  columnN datatype,);

Example:

Consider creating the COMPANY table as follows:

sqlite> CREATE TABLE COMPANY(  ID INTEGER PRIMARY KEY  AUTOINCREMENT,  NAME      TEXT   NOT NULL,  AGE      INT    NOT NULL,  ADDRESS    CHAR(50),  SALARY     REAL);

Now, the following records are inserted into the table COMPANY:

INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'Paul', 32, 'California', 20000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ('Allen', 25, 'Texas', 15000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ('Teddy', 23, 'Norway', 20000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'Mark', 25, 'Rich-Mond ', 65000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'David', 27, 'Texas', 85000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'Kim', 22, 'South-Hall', 45000.00 );INSERT INTO COMPANY (NAME,AGE,ADDRESS,SALARY)VALUES ( 'James', 24, 'Houston', 10000.00 );

This will be inserted into the COMPANY 7 tuples, and COMPANY will have the following records:

ID NAME AGE ADDRESS SALARY
--------------------------------------------------
1 Paul 32 California 20000.0
2 Allen 25, Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27, Texas 85000.0
6. Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0

Personal Understanding:

1. Database insert fields:

AUTOINCREMENT (auto-increment field) cannot reuse the id value of the deleted field to ensure that the id must be unique;
Rowid is the maximum rowid + 1 that already exists. It is possible that rowid + 1 (current rowid) has been deleted before;

2. After the database rowid reaches the maximum value:

AUTOINCREMENT (an auto-increment field) returns the SQLITE_FULL error code;
The new rowid value will randomly find an unused field id value before the maximum number, which may be a previously deleted field;