Room自动迁移（重命名列）失败：NOT NULL约束异常及迁移类问题

背景 #

最近在Room实体中对列进行重命名，为列名添加setting_前缀。

自动迁移实现代码 #

class AppDatabase_AutoMigration_15_16_Impl extends Migration {
private final AutoMigrationSpec callback = new SettingsRenamedColumnsAutoMigration();

public AppDatabase_AutoMigration_15_16_Impl() {
    super(15, 16);
}

public void migrate(@NonNull SupportSQLiteDatabase database) {
    database.execSQL("ALTER TABLE `merchants` ADD COLUMN `accessLevel` INTEGER NOT NULL DEFAULT 0");
    database.execSQL("CREATE TABLE IF NOT EXISTS `merchantSettings` (`setting_id` INTEGER NOT NULL, `setting_group_id` INTEGER, `setting_type` TEXT NOT NULL, `setting_name` TEXT NOT NULL, `setting_value` TEXT NOT NULL, `setting_data_type` TEXT NOT NULL, `setting_created_at` INTEGER NOT NULL, `setting_updated_at` INTEGER NOT NULL, PRIMARY KEY(`setting_id`), FOREIGN KEY(`setting_group_id`) REFERENCES `merchants`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
    database.execSQL("CREATE INDEX IF NOT EXISTS `index_merchantSettings_setting_group_id` ON `merchantSettings` (`setting_group_id`)");
    database.execSQL("CREATE TABLE IF NOT EXISTS `_new_branchSettings` (`setting_id` INTEGER NOT NULL, `setting_branch_id` INTEGER, `setting_type` TEXT NOT NULL, `setting_name` TEXT NOT NULL, `setting_value` TEXT NOT NULL, `setting_data_type` TEXT NOT NULL, `setting_created_at` INTEGER NOT NULL, `setting_updated_at` INTEGER NOT NULL, PRIMARY KEY(`setting_id`), FOREIGN KEY(`setting_branch_id`) REFERENCES `branches`(`id`) ON UPDATE NO ACTION ON DELETE CASCADE )");
    database.execSQL("INSERT INTO `_new_branchSettings` (`setting_type`,`setting_updated_at`) SELECT `setting_type`,`updated_at` FROM `branchSettings`");
    database.execSQL("DROP TABLE `branchSettings`");
    database.execSQL("ALTER TABLE `_new_branchSettings` RENAME TO `branchSettings`");
    database.execSQL("CREATE INDEX IF NOT EXISTS `index_branchSettings_setting_branch_id` ON `branchSettings` (`setting_branch_id`)");
    DBUtil.foreignKeyCheck(database, "branchSettings");
    database.execSQL("CREATE TABLE IF NOT EXISTS `_new_branches` (`id` INTEGER NOT NULL, `name` TEXT NOT NULL, `address` TEXT, `lat` TEXT, `lng` TEXT, `is_default` INTEGER, `email` TEXT, `phone` TEXT, `clover_category_id` TEXT, `clover_gift_item` TEXT, `clover_cashless_item` TEXT, `prep_time` INTEGER, `opening_hours` TEXT, `currency_code` TEXT, `created_at` INTEGER NOT NULL, `updated_at` INTEGER NOT NULL, PRIMARY KEY(`id`))");
    database.execSQL("INSERT INTO `_new_` (`id`,`name`,`address`,`lat`,`lng`,`is_default`,`email`,`phone`,`prep_time`,`opening_hours`,`currency_code`,`created_at`,`updated_at`) SELECT `id`,`name`,`address`,`lat`,`lng`,`is_default`,`email`,`phone`,`prep_time`,`opening_hours`,`currency_code`,`created_at`,`updated_at` FROM `branches`");
    database.execSQL("DROP TABLE `branches`");
    database.execSQL("ALTER TABLE `_new_` RENAME TO `branches`");
    this.callback.onPostMigrate(database);
}

自动迁移规范代码 #

AutoMigration(
        from = 15,
        to = 16,
        spec = AppDatabase.SettingsRenamedColumnsAutoMigration::class
    )


    @RenameColumn.Entries(
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "id",
        toColumnName = "setting_id"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "branch_id",
        toColumnName = "setting_branch_id"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "name",
        toColumnName = "setting_name"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "value",
        toColumnName = "setting_value"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "data_type",
        toColumnName = "setting_data_type"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "created_at",
        toColumnName = "setting_created_at"
    ),
    RenameColumn(
        tableName = "branchSettings",
        fromColumnName = "updated_at",
        toColumnName = "setting_updated_at"
    ),
)
@DeleteColumn.Entries(
    DeleteColumn(tableName = "branches", columnName = "checkout_category_id")
)
class SettingsRenamedColumnsAutoMigration : AutoMigrationSpec

报错信息 #

所有更新用户均触发以下异常：

android.database.sqlite.SQLiteConstraintException: NOT NULL constraint failed: _new_branchSettings.setting_name (code 1299 SQLITE_CONSTRAINT_NOTNULL)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:831)
    at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:756)
    at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:66)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1806)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1733)
    at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.execSQL(FrameworkSQLiteDatabase.java:255)
    at loylap.core.sdk.data.local.db.AppDatabase_AutoMigration_15_16_Impl.migrate(AppDatabase_AutoMigration_15_16_Impl.java:31)
    at androidx.room.RoomOpenHelper.onUpgrade(RoomOpenHelper.kt:91)

已发现的问题 #

虽然我对MySQL/Room了解有限，但已定位到15→16迁移类中的两处明显错误：

• 列迁移不完整：创建_new_branchSettings表后，仅插入setting_type和setting_updated_at两列，未迁移所有必填列，导致setting_name的NOT NULL约束触发异常。
• 表名拼写错误：INSERT INTO new``中的目标表名拼写错误，正确应为_new_branches。

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