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)

The counter never regresses. It always generates max(all_existing_ids) + 1.

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 (only by DROP TABLE + recreate)