AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY - SQLite & Android

Question

I'm trying to create a table in my DB with an ID that is autoincrement itself but whenever I try to add AUTOINCREMENT keyword to my query it tells me that :

AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

Here is my query:

@Override
public void onCreate(SQLiteDatabase db) {
    String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_TASKS + " ( "
            + KEY_ID + "INTEGER PRIMARY KEY AUTOINCREMENT, "
            + KEY_NOTETITLE + " TEXT, " + KEY_NOTECONTENT + " Text, "
            + KEY_STATUS + " INTEGER)";
    db.execSQL(sql);
}

I have also tried to write AUTO_INCREMENT but then I got syntax error.

I found out that this is the source of the problem because whenever I try to remove AUTOINCREMENT, it works fine.

So... what do you think is the problem?

Mannerless

Related: If you want to create an auto-incrementing non-primary key column in SQLite (Google brought me here), then see this answer. – Delapaz

Answer 1

You need to add a space between KEY_ID AND INTEGER

So change

+ KEY_ID + "INTEGER PRIMARY KEY AUTOINCREMENT, "

to

+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "

Corydalis

Answer 2

Create table with integer primary key no need to explicitly mention the AUTOINCREMENT

e.g.

CREATE TABLE t1(
  a INTEGER PRIMARY KEY,
  C TEXT
); 

Insert into t1(C) values("Hello");

Nomenclature

Without autoincrement sqlite may reuse deleted ids. – Caulis

@loshadvtapkah PRIMARY KEY will use unused integer greater than the last row id: source: sqlite.org/autoinc.html – Contemptuous

@RameshKumar yes, and it doesn't mean it wouldn't reuse ids. If I remove several records with the top ids, they will be reused. In most of the cases it is not a problem at all, but even if application is keeping track on foreign keys checks, it is better to not have reused keys, to avoid one bug due to other bug. – Caulis

Answer 3

Create Table like this put some space before INTEGER ....

"CREATE TABLE "+TABLE_NAME+" ("+KEY_ID+" INTEGER PRIMARY KEY AUTOINCREMENT)";

Nonobjective

Answer 4

I got the same error below:

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

Because I used TEXT type with PRIMARY KEY AUTOINCREMENT as shown below:

CREATE TABLE my_table (
  id TEXT PRIMARY KEY AUTOINCREMENT
  -- ↑↑↑↑
);

So, I used INTEGER type with PRIMARY KEY AUTOINCREMENT as shown below, then the error was solved:

CREATE TABLE my_table (
  id INTEGER PRIMARY KEY AUTOINCREMENT
  -- ↑↑↑↑↑↑↑
);

The doc says below:

Any attempt to use AUTOINCREMENT on a WITHOUT ROWID table or on a column other than the INTEGER PRIMARY KEY column results in an error.

In addition, if you use AUTOINCREMENT without INTEGER PRIMARY KEY as shown below:

CREATE TABLE my_table (
  id INTEGER AUTOINCREMENT
          -- ↑↑↑↑↑↑↑↑↑↑↑↑↑
);

Then, you get the error below:

Parse error: near "autoincrement": syntax error

Weslee