SQLite FAQ: How to create an autoincrement field in SQLite

SQLite FAQ: How do I create an autoincrement field in SQLite?

SQLite autoincrement solution

You define a SQLite autoincrement field — also known in other databases as a serial, identity, or primary key field — with this syntax:

id INTEGER PRIMARY KEY

The PRIMARY KEY syntax is what defines the SQLite autoincrement field.

Fore more information, let’s look at an example.

Defining a SQLite autoincrement field in a table

Here’s what this SQLite autoincrement syntax looks like in a complete SQLite database CREATE TABLE definition:

CREATE TABLE salespeople (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    commission_rate REAL NOT NULL
);

Inserting data with a SQLite autoincrement field

When you have a database table with a SQLite autoincrement field, there are two ways to insert data into that table and automatically increment the primary key. One way is to specify a null value for the SQLite autoincrement field, like this:

INSERT INTO salespeople VALUES (null, 'Fred', 'Flinstone', 10.0);

A second way is to define the fields you want to insert in your query, intentionally skipping the autoincrement field, like this:

INSERT INTO salespeople (first_name, last_name, commission_rate) VALUES ('Fred', 'Flinstone', 10.0);