SQLite: Syntax error in CREATE TABLE statement

Question

I'm trying to execute this statement in onUpgrade method of database helper but I'm getting an error:

 database.execSQL("CREATE TABLE VEHICLE_HOURS(ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
            "MINUTES INTEGER, VEHICLE VARCHAR(255), ORDER INTEGER);");

ERROR

Caused by: android.database.sqlite.SQLiteException: near "ORDER": syntax error (code 1): , while compiling: CREATE TABLE VEHICLE_HOURS(ID INTEGER PRIMARY KEY AUTOINCREMENT, MINUTES INTEGER, VEHICLE VARCHAR(255), ORDER INTEGER);

Thanks.

order is a reserved word in SQL. Even if allowed, you should not sue it for a column name. — Gordon Linoff
Possible duplicate of What SQLite column name can be/cannot be? — Tharif

bereal bereal · Accepted Answer · 2016-02-01T12:08:20

ORDER is a reserved word in SQL. You could of course suppress that error by taking the name into quotes, like:

database.execSQL("CREATE TABLE VEHICLE_HOURS(ID INTEGER PRIMARY KEY AUTOINCREMENT, " +
        "MINUTES INTEGER, VEHICLE VARCHAR(255), \"ORDER\" INTEGER);");

but better just pick another column name, then nobody (including your future self) will hate you while maintaining this code.

SQLite: Syntax error in CREATE TABLE statement

1 Answers