CODEWITHSUNDEEP
androidsqliteandroid-room
ChumiestBucket
ChumiestBucket

Reputation: 1073

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

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:

SQLite alter table syntax UML

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.

Upvotes: 1

Views: 1597

Answers (1)

ChumiestBucket
ChumiestBucket

Reputation: 1073

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.

Upvotes: 1

Related Questions