AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

This error occurs when you try to use AUTOINCREMENT on a column that isn't INTEGER PRIMARY KEY. Learn about SQLite's AUTOINCREMENT requirements.

The AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY error means you're trying to use AUTOINCREMENT incorrectly.

Understanding the Error

Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

AUTOINCREMENT has strict requirements in SQLite.

Common Causes

1. Wrong Data Type

SQL

-- Wrong: TEXT can't use AUTOINCREMENT
CREATE TABLE users (
  id TEXT PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

-- Wrong: BIGINT isn't INTEGER
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

2. Not Primary Key

SQL

-- Wrong: must be PRIMARY KEY
CREATE TABLE users (
  id INTEGER AUTOINCREMENT,
  name TEXT
);

3. Composite Primary Key

SQL

-- Wrong: AUTOINCREMENT can't be part of composite key
CREATE TABLE order_items (
  id INTEGER AUTOINCREMENT,
  order_id INTEGER,
  PRIMARY KEY (id, order_id)
);

How to Fix It

Solution 1: Use INTEGER PRIMARY KEY

SQL

-- Correct syntax
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT
);

Solution 2: Skip AUTOINCREMENT

For most cases, you don't need AUTOINCREMENT:

SQL

-- This auto-assigns IDs too (but may reuse deleted IDs)
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT
);

INSERT INTO users (name) VALUES ('Alice');  -- Gets id = 1

Solution 3: Use Trigger for Other Types

SQL

CREATE TABLE users (
  id TEXT PRIMARY KEY,
  name TEXT
);

-- Generate UUID-like IDs
CREATE TRIGGER set_user_id
AFTER INSERT ON users
FOR EACH ROW
WHEN NEW.id IS NULL
BEGIN
  UPDATE users SET id = lower(hex(randomblob(16)))
  WHERE rowid = NEW.rowid;
END;

Solution 4: Generate IDs in Application

JAVASCRIPT

const { v4: uuid } = require('uuid');

function insertUser(db, name) {
  const id = uuid();
  db.run('INSERT INTO users (id, name) VALUES (?, ?)', [id, name]);
  return id;
}

INTEGER PRIMARY KEY vs AUTOINCREMENT

Both auto-generate IDs, but differently:

SQL

-- INTEGER PRIMARY KEY (without AUTOINCREMENT)
-- - Uses max(rowid) + 1
-- - May reuse IDs from deleted rows
-- - Slightly faster
CREATE TABLE t1 (id INTEGER PRIMARY KEY);

-- INTEGER PRIMARY KEY AUTOINCREMENT
-- - Uses max(rowid) + 1, tracked in sqlite_sequence table
-- - Never reuses IDs (monotonically increasing)
-- - Slightly more overhead
CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTOINCREMENT);

When to Use AUTOINCREMENT

Use it when:

IDs must never be reused (audit trails, references)
External systems cache IDs

Skip it when:

Performance is critical
ID reuse is acceptable
Table is frequently emptied

Best Practices

Use INTEGER PRIMARY KEY for most auto-ID needs
Add AUTOINCREMENT only when IDs can't be reused
Use application-generated IDs for distributed systems
Document your ID strategy for the team