If you’re getting an error that reads “AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY” in SQLite, it appears that you’re trying to define a column as an AUTOINCREMENT on a column that’s not defined as INTEGER PRIMARY KEY.

SQLite only allows us to use AUTOINCREMENT on INTEGER PRIMARY KEY columns.

To address this issue, be sure to make the column an INTEGER PRIMARY KEY if you need to use AUTOINCREMENT on it.

Example of Error

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

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

Output:

Parse error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

Here I tried to use AUTOINCREMENT on a non-INTEGER PRIMARY KEY column. In this case, I defined it as INT PRIMARY KEY instead of INTEGER PRIMARY KEY.

While it’s true that INT and INTEGER are basically the same type (INT has INTEGER affinity), when it comes to defining the primary key, there’s an important difference. There’s a difference between INTEGER PRIMARY KEY and INT PRIMARY KEY.

• INTEGER PRIMARY KEY has special meaning. When we define a column using this exact definition, it becomes an alias for the ROWID.
• When we use INT PRIMARY KEY (or any primary key definition other than INTEGER PRIMARY KEY) it does not become an alias for the ROWID.

When we define a primary key column as AUTOINCREMENT, this results in the column using the auto-incremented ROWID value.

Solution 1

One way to fix this issue is to define the primary key column as an INTEGER instead of INT (or whatever other data type you’re using):

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

This was a suitable solution for this scenario, because I had previously tried to define the table as INT.

Bear in mind that it must be INTEGER PRIMARY KEY AUTOINCREMENT exactly. It can’t be INT PRIMARY KEY AUTOINCREMENT (as we saw in the error) or BIGINT PRIMARY KEY AUTOINCREMENT or other similar definitions (it must use INTEGER).

Solution 2

In the event that you need the primary key to hold a non-ROWID value, then you’ll need to remove the AUTOINCREMENT attribute:

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

By doing this, you’ll eliminate the error, but you’ll also remove the ability for the column to automatically increment its value for each row. In this case you would need to use another solution to auto-increment the value (assuming you want to auto-increment it), which would depend on the value that’s required.