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:

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

Share it!

There’s just one person behind this website; if this article was helpful (or interesting), I’d appreciate it if you’d share it. Thanks, Al.

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.