By Alvin Alexander. Last updated: September 27, 2022
SQLite FAQ: How do I create an autoincrement field in SQLite?
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
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);