Room数据库迁移中如何修改索引？实体变更后的迁移咨询

Let’s tackle how to modify indices when migrating your Room database—this is a super common task, so I’ll walk you through the most typical scenarios with concrete examples.

First, a Quick Primer #

Room doesn’t auto-handle index changes between versions, so you’ll need to write explicit SQL commands in your Migration class. The key is to match the exact index changes between your old and new Entity definitions.

Common Index Change Scenarios & Migration Steps #

1. Adding a New Index #

Suppose your old Entity had no index on the email column, and your new Entity adds a unique index here:

Old Entity:

@Entity(tableName = "users")
data class OldUser(
    @PrimaryKey val id: Int,
    val email: String,
    val name: String
)

New Entity:

@Entity(
    tableName = "users",
    indices = [Index(value = ["email"], unique = true)]
)
data class NewUser(
    @PrimaryKey val id: Int,
    val email: String,
    val name: String
)

Migration Code:
You’ll need to create the new unique index in your migration:

val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Create unique index on email column
        database.execSQL(
            "CREATE UNIQUE INDEX IF NOT EXISTS `index_users_email` ON `users` (`email`)"
        )
    }
}

Don’t forget to update your RoomDatabase annotation to include this migration and bump the version:

@Database(entities = [NewUser::class], version = 2)
abstract class AppDatabase : RoomDatabase() {
    // DAOs here
}

2. Deleting an Existing Index #

If you’re removing an index that was present in the old Entity, you’ll drop it in the migration. For example, if your old Entity had an index on name and the new one doesn’t:

Migration Code:

val MIGRATION_2_3 = object : Migration(2, 3) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Drop the old index on name
        database.execSQL(
            "DROP INDEX IF EXISTS `index_users_name`"
        )
    }
}

3. Modifying an Existing Index (e.g., Changing Columns or Unique Constraint) #

Room doesn’t let you "alter" an index directly—you’ll need to drop the old one first, then create the new updated index. For example, if your old index was on name alone, and the new one is a composite index on name and age with a unique constraint:

Migration Code:

val MIGRATION_3_4 = object : Migration(3, 4) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Drop the old single-column index
        database.execSQL("DROP INDEX IF EXISTS `index_users_name`")
        // Create the new composite unique index
        database.execSQL(
            "CREATE UNIQUE INDEX IF NOT EXISTS `index_users_name_age` ON `users` (`name`, `age`)"
        )
    }
}

Important Notes #

• Index Names: Room generates index names using the pattern index_<tableName>_<columnNames> (joined with underscores for composite indices). You can find the exact name by looking at the generated Room code, or use IF NOT EXISTS/IF EXISTS to avoid errors.
• Unique Index Conflicts: If you’re adding a unique index, make sure there are no duplicate values in the target columns before running the migration—otherwise, the CREATE INDEX command will fail. You might need to clean up duplicate data first in the migration.
• Test Thoroughly: Always test migrations with production-like data to catch edge cases (like duplicate entries for unique indices).

内容的提问来源于stack exchange，提问作者boybeak