If you’re getting an error that reads “AUTOINCREMENT not allowed on WITHOUT ROWID tables” in SQLite, it appears that you’re trying to define a column as an AUTOINCREMENT in a WITHOUT ROWID table.

SQLite doesn’t allow us to create AUTOINCREMENT columns on WITHOUT ROWID tables.

To address this issue, either remove the AUTOINCREMENT attribute or remove the WITHOUT ROWID from the table definition.

Example of Error

Here’s an example of code that produces the error:

CREATE TABLE t1 (
    c1 INTEGER PRIMARY KEY AUTOINCREMENT,
    c2 INT
) WITHOUT ROWID;

Output:

Parse error: AUTOINCREMENT not allowed on WITHOUT ROWID tables

In this case the table has both AUTOINCREMENT and WITHOUT ROWID, which resulted in the error.

Solution 1

One way to fix this issue is to remove the AUTOINCREMENT attribute:

CREATE TABLE t1 (
    c1 INTEGER PRIMARY KEY,
    c2 INT
) WITHOUT ROWID;

This obviously means that the primary key column will not be an AUTOINCREMENT column.

Solution 2

If you really must have an AUTOINCREMENT column, then you’ll need to remove the WITHOUT ROWID:

CREATE TABLE t1 (
    c1 INTEGER PRIMARY KEY AUTOINCREMENT,
    c2 INT
);

This time we have an AUTOINCREMENT column, but the table is not a WITHOUT ROWID table. This makes sense, because SQLite needs the ROWID in order to auto-increment the value in the primary key column (when we use INTEGER PRIMARY KEY, the primary key becomes an alias for the ROWID). When we use WITHOUT ROWID, we’re telling SQLite not to generate a ROWID for the table, which means that it can’t auto-increment (it can’t auto-increment a non-existent value).