SQLite FAQ: 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 in other databases as a serial, identity, or primary key field) with this syntax:


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 (
  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);

Add new comment

The content of this field is kept private and will not be shown publicly.

Anonymous format

  • Allowed HTML tags: <em> <strong> <cite> <code> <ul type> <ol start type> <li> <pre>
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.