SQLite autoincrement Keywords (auto increment) _mssql

SQLite's autoincrement is a keyword that is automatically incremented for field values in the table. We can use the AutoIncrement keyword on a specific column name to achieve an automatic increase in the value of the field when we create the table.

Note: integer fields can be autoincrement using the keyword.

Grammar

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 the company table to be created as follows:

Sqlite> CREATE TABLE Company (
  ID INTEGER PRIMARY KEY  autoincrement,
  NAME      TEXT not   NULL,
  Age      INT not    NULL,
  address    CHAR (x),
  SALARY     real
);

The following records are now inserted into the table company:

INSERT into Company (name,age,address,salary)
VALUES (' Paul ', the ' California ', 20000.00);
INSERT into Company (name,age,address,salary)
VALUES (' Allen ', ', ' Texas ', 15000.00);
INSERT into Company (name,age,address,salary)
VALUES (' Teddy ', N, ' Norway ', 20000.00);
INSERT into Company (name,age,address,salary)
VALUES (' Mark ', ', ' Rich-mond ', 65000.00);
INSERT into Company (name,age,address,salary)
VALUES (' David ', ', ' Texas ', 85000.00);
INSERT into Company (name,age,address,salary)
VALUES (' Kim ', ' South-hall ', 45000.00);
INSERT into Company (name,age,address,salary)
VALUES (' James ', N, ' Houston ', 10000.00);

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

ID NAME Age Address SALARY
----------  ----------  ----------  ----------  ----------
1 Paul California 20000.0
2 Allen Texas 15000.0
3 Teddy Norway 20000.0
4 Mark Rich-mond 65000.0
5 David Texas 85000.0
6 Kim South-hall 45000.0
7 James Houston 10000.0

Personal Understanding:

1. Database Insert field:

AutoIncrement (since the added field) cannot reuse the ID value of the deleted field, guaranteeing that the ID must be unique;
rowID is to find the largest existing rowid+1, it is possible rowid+1 (current rowid) was deleted before;

2. After the database rowid reaches the maximum:

AutoIncrement (from the added field) returns the Sqlite_full error code;
The ROWID new value will randomly find an ID value of the unused field before the maximum number, possibly a previously deleted field;