Android Room新增主键列时迁移报错，如何解决？

问题概述 #

为folders_table添加自增主键id列，手动编写迁移代码后执行报错，系统提示预期存在id列，但实际表结构仍为旧版本（folderName作为主键，无id列）。

迁移代码 #

private val MIGRATION_1_2 = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        database.execSQL(
            """
            DROP TABLE IF EXISTS new_folders_table;
            CREATE TABLE IF NOT EXISTS new_folders_table (     
                folderName TEXT NOT NULL,
                infoForSaving TEXT NOT NULL,
                id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
            );
            INSERT INTO new_folders_table (folderName, infoForSaving)
            SELECT folderName, infoForSaving FROM folders_table;
            DROP TABLE IF EXISTS folders_table;
            ALTER TABLE new_folders_table RENAME TO folders_table;
            """
        )
    }
}

新旧实体类 #

旧架构实体类 #

@Entity(tableName = "folders_table")
data class FoldersTable(
    @PrimaryKey val folderName: String,
    val infoForSaving: String,
)

新架构实体类 #

@Entity(tableName = "folders_table")
data class FoldersTable(
    var folderName: String,
    var infoForSaving: String,

    @PrimaryKey(autoGenerate = true)
    var id: Long = 0
)

错误信息 #

Expected:
TableInfo{name='folders_table', columns={infoForSaving=Column{name='infoForSaving', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, folderName=Column{name='folderName', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}, id=Column{name='id', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}}, foreignKeys=[], indices=[]}
Found:
TableInfo{name='folders_table', columns={folderName=Column{name='folderName', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=1, defaultValue='undefined'}, infoForSaving=Column{name='infoForSaving', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='undefined'}}, foreignKeys=[], indices=[]}

解决方案 #

1. 确保迁移类已注册到RoomDatabase #

这是最常见的问题：编写了迁移代码但未在Room数据库构建器中注册，导致手动迁移未执行，Room尝试自动迁移失败。

修改RoomDatabase类，添加.addMigrations(MIGRATION_1_2)：

@Database(entities = [FoldersTable::class], version = 2)
abstract class AppDatabase : RoomDatabase() {
    // 定义DAO接口
    abstract fun foldersDao(): FoldersDao

    companion object {
        // 迁移实例
        private val MIGRATION_1_2 = object : Migration(1, 2) {
            override fun migrate(database: SupportSQLiteDatabase) {
                // 迁移SQL代码
                database.execSQL(
                    """
                    DROP TABLE IF EXISTS new_folders_table;
                    CREATE TABLE IF NOT EXISTS new_folders_table (     
                        folderName TEXT NOT NULL,
                        infoForSaving TEXT NOT NULL,
                        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
                    );
                    INSERT INTO new_folders_table (folderName, infoForSaving)
                    SELECT folderName, infoForSaving FROM folders_table;
                    DROP TABLE IF EXISTS folders_table;
                    ALTER TABLE new_folders_table RENAME TO folders_table;
                    """
                )
            }
        }

        // 获取数据库实例
        fun getInstance(context: Context): AppDatabase {
            return Room.databaseBuilder(
                context.applicationContext,
                AppDatabase::class.java,
                "app_database"
            )
                .addMigrations(MIGRATION_1_2) // 必须注册迁移
                .build()
        }
    }
}

2. 验证迁移SQL语法 #

将迁移中的SQL语句复制到SQLite浏览器或其他SQLite工具中执行，检查是否能正确生成新表、插入数据并完成重命名。确保没有语法错误（比如遗漏分号、引号错误等）。

3. 清除应用数据后重新测试 #

如果之前迁移失败导致数据库处于异常状态，清除应用的存储数据，重新启动应用执行迁移，避免旧的错误表结构干扰。

4. 可选：调整主键生成策略 #

Room中@PrimaryKey(autoGenerate = true)在SQLite中默认会生成INTEGER PRIMARY KEY（自动递增），无需显式添加AUTOINCREMENT关键字（该关键字仅用于强制不重用已删除的主键值）。可以尝试修改迁移SQL中的主键定义为：

id INTEGER PRIMARY KEY NOT NULL

这与Room自动生成的SQL保持一致，可能避免潜在的兼容性问题。

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