SQLite AUTOINCREMENT

In this article we will show how SQLite AUTOINCREMENT is defined and what it is used for.

The keyword SQLite AUTOINCREMENT is used when creating a table in the database, it is assigned to a column to automatically increase the respective column with integer values.

SQLite AUTOINCREMENT is used most often when the column is of type INTEGER, the column defined as the PRIMARY KEY in a table.

Using AUTOINCREMENT in the SQLite database causes the use of additional resources such as CPU, memory, disk space, and disk I/O overhead. SQLite AUTOINCREMENT should be avoided unless strictly necessary. It is usually not necessary.

Syntax

Here is the syntax of SQLite AUTOINCREMENT:

CREATE TABLE table_name(
	column_name1 datatype PRIMARY KEY AUTOINCREMENT,
	column_name2 datatype NOT NULL,
	column_name3 datatype,
	...
);

Example

CREATE TABLE customer_type(
type_id INTEGER PRIMARY KEY AUTOINCREMENT,
type_name VARCHAR(100) NOT NULL,
comments VARCHAR(1000)
);

INSERT INTO customer_type (type_name, comments)
VALUES ('Individual', 'test 1');

INSERT INTO customer_type (type_name, comments)
VALUES ('Company', 'test 2');

INSERT INTO customer_type (type_name, comments)
VALUES ('Corporation', 'test 3');

INSERT INTO customer_type (type_name, comments)
VALUES ('Syndicate', 'test 4');

INSERT INTO customer_type (type_name, comments)
VALUES ('Institution', 'test 5');

SELECT * FROM customer_type;

Table of customer_type