Auto Increment

Auto increment automatically generates sequential integer values for primary key columns when no explicit value is provided.

Syntax

CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name TEXT NOT NULL,
    price FLOAT
);

Both AUTO_INCREMENT and AUTOINCREMENT are accepted.

Usage

Omit the auto-increment column to generate the next value:

INSERT INTO products (name, price) VALUES ('Widget', 9.99);
-- id = 1 (auto-generated)

INSERT INTO products (name, price) VALUES ('Gadget', 19.99);
-- id = 2 (auto-generated)

Explicit Values

You can still provide explicit values for auto-increment columns:

INSERT INTO products (id, name, price) VALUES (100, 'Special', 49.99);
-- id = 100 (explicitly set)

INSERT INTO products (name, price) VALUES ('Next', 5.99);
-- id = 101 (counter updated to max + 1)

Counter Behavior

The auto-increment counter always tracks the maximum ID seen:

INSERT INTO products (id, name, price) VALUES (1, 'A', 10);    -- id = 1
INSERT INTO products (id, name, price) VALUES (100, 'B', 20);  -- id = 100
INSERT INTO products (name, price) VALUES ('C', 30);            -- id = 101
INSERT INTO products (id, name, price) VALUES (50, 'D', 40);   -- id = 50 (explicit)
INSERT INTO products (name, price) VALUES ('E', 50);            -- id = 102 (not 51)

On its own the counter never regresses. It generates one past the highest id the table has held, deleted rows included. Only ALTER TABLE ... AUTO_INCREMENT lowers it.

Setting the Next Value

ALTER TABLE ... AUTO_INCREMENT sets the counter so the next generated id is the given value:

ALTER TABLE products AUTO_INCREMENT = 1000;
INSERT INTO products (name, price) VALUES ('F', 60);            -- id = 1000

A value at or below the largest id a row holds gives one past that row instead, which lowers the counter after deletes and rolled-back inserts. See ALTER TABLE.

Constraints

  • Auto increment is only supported on INTEGER PRIMARY KEY columns
  • The counter is maintained per table
  • Gaps in the sequence are allowed (e.g., after deletions or explicit inserts)
  • The counter is not reset by DELETE, nor by closing and reopening a file database; TRUNCATE TABLE and DROP TABLE + recreate start it again at 1