Android Room, SQLite - TO expected, got 'COLUMN'

Question

I'm trying to rename a column in my Room database. I foolishly used the column name index and want to change it to id, but this migration function is causing headaches:

static final Migration MIGRATION_2_3 = new Migration(2, 3) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            database.execSQL("ALTER TABLE items RENAME COLUMN index TO id;");
        }
    };

I followed this syntax diagram for the query:

Android Studio is giving me the error TO expected, got 'COLUMN', and I cannot use the database due to the RuntimeException:

Caused by: java.lang.IllegalStateException: Room cannot verify the data integrity. Looks like you've changed schema but forgot to update the version number. You can simply fix this by increasing the version number.

The version number is correct, so I am assuming this issue is caused by the syntax problem above; I cannot find anything else wrong with my setup.

Android uses SQLite v3.19. That makes renaming a column using RENAME COLUMN not possible. The best approach is to recreate the table. — Leonardo Velozo, Oct 16 '18 at 16:11
[How do I rename a column in a SQLite database table?](https://stackoverflow.com/questions/805363/how-do-i-rename-a-column-in-a-sqlite-database-table/52346199#52346199) Probably you are using lower version than 3.25 — Lukasz Szozda, Oct 16 '18 at 16:12
`index` must be [quoted](https://www.sqlite.org/lang_keywords.html). — CL., Oct 16 '18 at 19:35
@CL. yes, I would add some backticks but I'd much rather just have a proper name for the column — ChumiestBucket, Oct 16 '18 at 19:40
I meant, in the ALTER TABLE statement. (You ran into another error before that.) — CL., Oct 16 '18 at 19:44

ChumiestBucket · Answer 1 · 2018-10-16T22:36:58.370

Android uses SQLite v3.19. That makes renaming a column using RENAME COLUMN not possible. The best approach is to recreate the table. – Leo Pelozo

Looks like I need to create function that drops the table so I can create a new one.

@Query("DELETE FROM items")
void dropTable();

... then create the table again, though I'm not sure how to go about this.

Update:

I was able (I think, we'll see...) to re-create the table by calling the above function, removing ALL migrations and setting the database version back to 1. Then I re-defined the database class itself with the proper names etc. and was able to insert data into it without any errors. Adding .fallbackToDestructiveMigration() to my database singleton class was also necessary.

Personally I think this is a little ridiculous just for simply re-naming a column; I was never able to simply rename the column and add a migration for the change, nor was I able to drop the table and re-create it with the proper column name and add that as a migration. But alas, this is Android after all.

Android Room, SQLite - TO expected, got 'COLUMN'

1 Answers1