SQlite exception when trying to create table in android

Question

I'm following the guide at http://developer.android.com/training/basics/data-storage/databases.html

with minor changes to fit my needs, but my application crashes as soon as it tries to create the table. Logcat displays a sqlite exception, syntax error near index. Even though I've tripled checked and the syntax looks correct.

The exception reads: near "index": syntax error (code 1): , while compiling: CREATE TABLE my_locations ( index INTEGER PRIMARY KEY, loc_name TEXT, loc_id TEXT);

Here is my code

private static final String TEXT_TYPE = " TEXT";
    private static final String COMMA_SEP = ", ";
    private static final String SQL_CREATE_ENTRIES =
        "CREATE TABLE " + FeedEntry.TABLE_NAME + " ( " +
        FeedEntry.COLUMN_NAME_INDEX + " INTEGER PRIMARY KEY, " +
        FeedEntry.COLUMN_NAME_LOC_NAME + TEXT_TYPE + COMMA_SEP +
        FeedEntry.COLUMN_NAME_LOC_ID + TEXT_TYPE +
        ");";

    private static final String SQL_DELETE_ENTRIES =
        "DROP TABLE IF EXISTS " + FeedEntry.TABLE_NAME;

    // If you change the database schema, you must increment the database version.
    public static final int DATABASE_VERSION = 1;
    public static final String DATABASE_NAME = "PersonalLocations.db";

    public PersonalLocationsDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(SQL_CREATE_ENTRIES);
    }

Phantômaxx · Accepted Answer · 2014-07-01 17:31:37Z

2

INDEX is a SQLite reserved word and can't be used as a name.

Reference: http://www.sqlite.org/lang_keywords.html

So just rename index to id (by convention, it's _id) or something you like better.
_id is needed by several database helper methods (but you can use the rowID AS _id trick).

You could also skip explicitly adding an id field, since there's the hidden rowId field that can be used as a replacement.

At least, for small structures (1 table and a few fields or such).

edited Jul 1, 2014 at 17:31

answered Jul 1, 2014 at 17:15

Phantômaxx

38.1k21 gold badges88 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Giru Bhai · Accepted Answer · 2014-07-01 17:16:27Z

1

You can not use reserved words in sqlite table column name,as index is reserved word.

answered Jul 1, 2014 at 17:16

Giru Bhai

14.4k5 gold badges50 silver badges74 bronze badges

Collectives™ on Stack Overflow

SQlite exception when trying to create table in android

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related