SQLite autoincrement - How to create an autoincrement field in SQLite

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

You define a SQLite autoincrement field (also known as a serial, identity, or primary key field) with this syntax:

id INTEGER PRIMARY KEY,

SQLite autoincrement field in a table

Here's what this SQLite autoincrement syntax looks like in a complete SQLite database 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);