Runnable SQLite Docs: Primary Keys

Q: How do I define a primary key in SQLite?

Add PRIMARY KEY to a column in your CREATE TABLE statement, e.g. id INTEGER PRIMARY KEY. For a key that spans multiple columns, use a table-level constraint: PRIMARY KEY (cola, colb). The column or combination must be unique across rows.

Q: What's the difference between INTEGER PRIMARY KEY and other primary keys in SQLite?

INTEGER PRIMARY KEY is special: it becomes an alias for the table's built-in rowid, so it's stored directly in the B-tree with no extra index. Any other type, or a composite key, gets a separate unique index. For single-column numeric ids, INTEGER PRIMARY KEY is faster and smaller.

Q: Do I need AUTOINCREMENT on a SQLite primary key?

Usually no. An INTEGER PRIMARY KEY already auto-assigns a unique rowid when you insert NULL. AUTOINCREMENT only adds a guarantee that ids never get reused after deletion — at the cost of an extra sqlite_sequence table. Skip it unless you specifically need that monotonic-id behavior.

What a Primary Key Actually Does

A primary key is the column (or combination of columns) that uniquely identifies each row in a table. Two rows can't share the same primary key value. SQLite enforces that for you, and it uses the key to find rows quickly.

The simplest form goes inline on a column:

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    name  TEXT
);

INSERT INTO users (email, name) VALUES ('ada@example.com', 'Ada');
INSERT INTO users (email, name) VALUES ('boris@example.com', 'Boris');

SELECT * FROM users;

You didn't supply an id and SQLite filled one in. That's not magic — it's a special case for INTEGER PRIMARY KEY that's worth understanding before you write anything else.

INTEGER PRIMARY KEY Is Special

In most databases, a primary key is just a unique index. In SQLite, every regular table already has a hidden 64-bit integer called the rowid that identifies rows internally. When you declare a column as exactly INTEGER PRIMARY KEY, that column becomes the rowid. No extra index, no extra storage — your id and the row's physical location are the same thing.

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT
);

INSERT INTO posts (title) VALUES ('First'), ('Second'), ('Third');

SELECT id, rowid, title FROM posts;

id and rowid are the same column under two names. Lookups by id go straight to the row — there's no second tree to walk. This is why the standard advice for SQLite is: if you want a numeric primary key, write INTEGER PRIMARY KEY exactly. Not INT, not BIGINT, not INTEGER NOT NULL PRIMARY KEY (well, that one works, but the type must be INTEGER).

Other types still work — they just get a separate unique index, which is fine, just not as compact.

AUTOINCREMENT Is Usually Unnecessary

A common reflex from other databases is to write id INTEGER PRIMARY KEY AUTOINCREMENT. In SQLite, the AUTOINCREMENT keyword does something narrower than the name suggests, and most of the time you don't need it.

Without AUTOINCREMENT, an INTEGER PRIMARY KEY column auto-fills with one more than the largest existing rowid. If you delete the last row, the next insert may reuse that id.

With AUTOINCREMENT, SQLite tracks the highest id ever used in a side table called sqlite_sequence and never reuses values, even after deletion.

CREATE TABLE plain (id INTEGER PRIMARY KEY, label TEXT);
CREATE TABLE strict_id (id INTEGER PRIMARY KEY AUTOINCREMENT, label TEXT);

INSERT INTO plain (label) VALUES ('a'), ('b'), ('c');
INSERT INTO strict_id (label) VALUES ('a'), ('b'), ('c');

DELETE FROM plain WHERE id = 3;
DELETE FROM strict_id WHERE id = 3;

INSERT INTO plain (label) VALUES ('new');
INSERT INTO strict_id (label) VALUES ('new');

SELECT 'plain' AS table_name, id, label FROM plain
UNION ALL
SELECT 'strict_id', id, label FROM strict_id;

The plain table reused id 3. The AUTOINCREMENT table jumped to 4. Unless you have a real reason to forbid id reuse — auditing, external references that linger after deletion — skip AUTOINCREMENT. It costs an extra write per insert and a separate bookkeeping table.

Composite Primary Keys

Sometimes a single column isn't enough. A join table that maps users to roles, for example, is uniquely identified by the pair (user_id, role_id). For that, declare the key at the table level:

CREATE TABLE user_roles (
    user_id  INTEGER NOT NULL,
    role_id  INTEGER NOT NULL,
    granted_at TEXT DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id)
);

INSERT INTO user_roles (user_id, role_id) VALUES (1, 10), (1, 20), (2, 10);
-- This one violates the composite key:
INSERT INTO user_roles (user_id, role_id) VALUES (1, 10);

The pair has to be unique across the whole table — (1, 10) can only appear once. Either column on its own can repeat freely. That's the whole point: each user can have many roles, each role can have many users, but a given user-role pairing exists at most once.

A composite primary key creates a separate index covering the listed columns. It does not become the rowid — only a single INTEGER PRIMARY KEY gets that treatment.

The NULL-in-Primary-Key Footgun

Here's a quirk that surprises people coming from PostgreSQL or MySQL: in a regular SQLite table, a primary key column other than INTEGER PRIMARY KEY can contain NULLs. It's a long-standing bug that the SQLite authors kept for backward compatibility.

CREATE TABLE products (
    sku   TEXT PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Apple');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystery 1');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystery 2');

SELECT * FROM products;

Two rows with NULL slipped past the primary key. The fix is to add NOT NULL explicitly on every non-integer primary key column:

CREATE TABLE products (
    sku   TEXT NOT NULL PRIMARY KEY,
    name  TEXT
);

INSERT INTO products (sku, name) VALUES ('A1', 'Apple');
INSERT INTO products (sku, name) VALUES (NULL, 'Mystery');  -- now rejected

Or use a STRICT table, where the NULL-in-PK bug is corrected. The habit of writing NOT NULL on every primary key column is cheap insurance.

Primary Key vs UNIQUE

Both prevent duplicates. The differences:

A table has at most one primary key, but can have many UNIQUE constraints.
The primary key is the table's "main" identifier — foreign keys point at it by default.
An INTEGER PRIMARY KEY becomes the rowid; a UNIQUE integer column does not.
UNIQUE columns happily accept multiple NULLs (each NULL is considered distinct).

CREATE TABLE accounts (
    id       INTEGER PRIMARY KEY,
    email    TEXT NOT NULL UNIQUE,
    username TEXT NOT NULL UNIQUE
);

INSERT INTO accounts (email, username) VALUES ('a@x.com', 'ada');
INSERT INTO accounts (email, username) VALUES ('b@x.com', 'boris');
SELECT * FROM accounts;

id is the row's identity. email and username are also unique, but they're business attributes — they could change, while the id shouldn't.

Adding a Primary Key Later (Mostly: Don't)

SQLite's ALTER TABLE is limited. You can't run ALTER TABLE ... ADD PRIMARY KEY — that statement doesn't exist. If you forgot the primary key and the table already has data, the path is to recreate it:

CREATE TABLE events (
    name TEXT,
    occurred_at TEXT
);
INSERT INTO events VALUES ('login', '2026-01-01'), ('logout', '2026-01-02');

-- Rebuild with a primary key:
CREATE TABLE events_new (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    occurred_at TEXT NOT NULL
);
INSERT INTO events_new (name, occurred_at) SELECT name, occurred_at FROM events;
DROP TABLE events;
ALTER TABLE events_new RENAME TO events;

SELECT * FROM events;

This is the standard SQLite migration dance. Wrap it in a transaction in real code, and disable foreign keys briefly if other tables reference this one. The lesson: get the primary key right at CREATE TABLE time.

A Quick Checklist

When you write a new table, ask:

Does this row have a natural unique id? If it's a single integer, use INTEGER PRIMARY KEY.
Is the identity actually a combination of columns (a join table)? Use a table-level PRIMARY KEY (col_a, col_b).
Is the key text or another non-integer type? Add NOT NULL explicitly.
Do you actually need AUTOINCREMENT? Probably not.
Is the table small and read-mostly with a non-integer primary key? Consider WITHOUT ROWID (covered with the rowid doc).

Next: rowid

INTEGER PRIMARY KEY made a brief appearance as "an alias for rowid" — but rowid is the foundation under every regular SQLite table, and it's worth understanding directly. That's the next page.

Frequently Asked Questions

How do I define a primary key in SQLite?

Add PRIMARY KEY to a column in your CREATE TABLE statement, e.g. id INTEGER PRIMARY KEY. For a key that spans multiple columns, use a table-level constraint: PRIMARY KEY (col_a, col_b). The column or combination must be unique across rows.

What's the difference between INTEGER PRIMARY KEY and other primary keys in SQLite?

INTEGER PRIMARY KEY is special: it becomes an alias for the table's built-in rowid, so it's stored directly in the B-tree with no extra index. Any other type, or a composite key, gets a separate unique index. For single-column numeric ids, INTEGER PRIMARY KEY is faster and smaller.

Do I need AUTOINCREMENT on a SQLite primary key?

Usually no. An INTEGER PRIMARY KEY already auto-assigns a unique rowid when you insert NULL. AUTOINCREMENT only adds a guarantee that ids never get reused after deletion — at the cost of an extra sqlite_sequence table. Skip it unless you specifically need that monotonic-id behavior.

Why does my SQLite primary key allow NULL values?

Historical bug, preserved for compatibility: in regular tables, a non-INTEGER primary key column can accept NULLs unless you explicitly add NOT NULL. The INTEGER PRIMARY KEY column is the exception — it never allows NULL. To be safe, write NOT NULL on every primary key column, or use a STRICT table where the rule is enforced properly.

Related concepts

SQLite Primary Keys: INTEGER, Composite, and AUTOINCREMENT