(1) near "AUTO_INCREMENT": syntax error when altering table

Question

I'm trying to alter an SQLite table defined as follows:

    String CREATE_TABLE="CREATE TABLE IF NOT EXISTS " + tab_NAME + " ("
                            + row_ID + " INTEGER PRIMARY KEY , "
                            + row_NAME + " TEXT, "
                            + row_EMAIL + " TEXT, "
                            + row_WEBSITE + " TEXT, "
                            + row_TELEPHONE1 + " TEXT, "
                            + row_TELEPHONE2 + " TEXT, "
                            + row_TELEPHONE3 + " TEXT, "
                            + row_TELEPHONE4 + " TEXT, "
                            + row_TELEPHONE5 + " TEXT);";
        db_name.execSQL(CREATE_TABLE);

        String newTab="CREATE TABLE IF NOT EXISTS temp ("
                + row_ID + " INTEGER PRIMARY KEY AUTOINCREMENT , "
                + row_NAME + " TEXT, "
                + row_EMAIL + " TEXT, "
                + row_WEBSITE + " TEXT, "
                + row_TELEPHONE1 + " TEXT, "
                + row_TELEPHONE2 + " TEXT, "
                + row_TELEPHONE3 + " TEXT, "
                + row_TELEPHONE4 + " TEXT, "
                + row_TELEPHONE5 + " TEXT);";
        db_name.execSQL(newTab);

And here is the auto_increment alter statement:

    String alter="ALTER TABLE temp AUTO_INCREMENT=1;";
    ourDatabase.execSQL(alter);

I got an error saying (1) near AUTO_INCREMENT: syntax error

what's wrong?? any help??

score 22 · Answer 1 · edited Aug 26 '23 at 22:21

22

In SQLite a column declared INTEGER PRIMARY KEY will autoincrement by itself. Remove the AUTOINCREMENT keyword and then try.

Check the SQLite FAQ

edited Aug 26 '23 at 22:21

desertnaut

57,590
26
140
166

answered Dec 23 '12 at 14:59

Abhishek Sabbarwal

3,758
1
26
41

ok, I removed the ` String alter="ALTER TABLE temp AUTO_INCREMENT=1;";` ` ourDatabase.execSQL(alter);` and it worked well, but I need to reset the auto_increment value to 1, so what to do ? – Ahlam Dec 23 '12 at 15:51
3

Drop the table and re-create it. – Abhishek Sabbarwal Dec 23 '12 at 15:55
1

Also you could accept this as answer if your error was resolved :) – Abhishek Sabbarwal Dec 23 '12 at 15:59
better solution than dropping the table: https://stackoverflow.com/a/1601854/1937033 – ThePhi Dec 30 '18 at 07:16

score 7 · Answer 2 · edited Aug 26 '23 at 22:22

7

In sqlite setting column as PRIMARY KEY is an AUTOINCREMENT. So there is no need of specifying explicitly column as AUTOINCREMENT. Check the SQLite FAQ.

edited Aug 26 '23 at 22:22

desertnaut

57,590
26
140
166

answered Dec 24 '12 at 11:14

arthankamal

6,341
4
36
51

(1) near "AUTO_INCREMENT": syntax error when altering table

2 Answers2

Linked