SQLite - AUTOINCREMENT

The SQLite AUTOINCREMENT is a keyword used for auto incrementing a value of a field in the table. The AUTOINCREMENT keyword can be used with INTEGER PRIMARY KEY field only. It can be applied to a field when creating a table.

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

In SQLite, a column with type INTEGER PRIMARY KEY is an alias for the ROWID (except in WITHOUT ROWID tables) which is always a 64-bit signed integer.

On an INSERT, if the ROWID or INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer, usually one more than the largest ROWID currently in use. This is true regardless of whether or not the AUTOINCREMENT keyword is used.

If the AUTOINCREMENT keyword appears after INTEGER PRIMARY KEY, that changes the automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database. In other words, the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

SQLite AUTOINCREMENT statement

The below mentioned statement creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which auto-increment is applied on column EmpID.

CREATE TABLE Employee (
  EmpID INTEGER PRIMARY KEY AUTOINCREMENT,
  Name VARCHAR(255),
  City VARCHAR(100),
  Age INT,
  Salary DECIMAL(18,2)
);

While inserting a new record, EmpID should not be specified. A unique value of EmpID will be added automatically.

INSERT INTO Employee (Name, City, Age, Salary)
VALUES ('John', 'London', 27, 2800);

The statement mentioned above will insert a new record into the "Employee" table. The "EmpID" column will be assigned a unique value automatically. The "Name" column will be set to "John", the "City" column will be set to "London", the "Age" column will be set to 27 and the "Salary" column will be set to 2800.