Search code examples
androidandroid-studioandroid-sqlite

Android Studio 3.0 canary 1 : SQL syntax error

I have a problem after upgraded to Android Studio 3.0 canary 1 from Android Studio 2.3

db.execSQL("CREATE TABLE IF NOT EXISTS " + Contract.COUNTRY_PATH + " (" +
                Contract.Country._ID + " INTEGER AUTO INCREMENT , " +
                Contract.Country.COLUMN_CITY_ID + " INTEGER PRIMARY KEY," +
                Contract.Country.COLUMN_COUNTRY_NAME + " TEXT," +
                Contract.Country.COLUMN_SUNRISE + " INTEGER," +
                Contract.Country.COLUMN_SUNSET + " INTEGER) ");

It shows an error.

'(',')',<column constraint> or comma expected ,got 'AUTO'

It was fine with Android Studio 2.3. Any suggestions. Thanks.

Solution

  • The SQL syntax checker in Android Studio 3 is stricter than sqlite itself.

    INTEGER AUTO INCREMENT in sqlite itself basically just results in a column with integer affinity. The mistyped AUTO INCREMENT is just noise. That's why it gets flagged in Studio but does not cause a syntax error in sqlite.

    If you want an autoincrementing column, make it INTEGER PRIMARY KEY. If you really need the rowid reuse avoidance, then the column should be INTEGER PRIMARY KEY AUTOINCREMENT. Note that you can only have one primary key column in a table. See also: https://sqlite.org/autoinc.html