How to Migrate Not Null table column into Null in Android Room database

Question

I'm new to android room library. I need to migrate a Not Null column to Null, But room migration only allow ADD or RENAME in ALTER table query. How do execute a column migration query?

@Entity(tableName = "vehicle_detail")
data class VehicleDetailsEntity(
    @PrimaryKey(autoGenerate = true)
    val vehicleClientId: Long = 0,
    val vehicleId: String,
    val updatedOn: Date,
    val updatedBy: String
)

I need to change table structure into

@Entity(tableName = "vehicle_detail")
data class VehicleDetailsEntity(
    @PrimaryKey(autoGenerate = true)
    val vehicleClientId: Long = 0,
    val vehicleId: String,
    val updatedOn: Date?,
    val updatedBy: String?
)

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.

Answer 1

You need to run a migration since SQLite doesn't allow column constraint modification.

For that migration you need to create a new temp table and copy all your previous data to it, then delete the old table and rename the temp one to the needed table name.

If you have a scheme directory, you can find your exact creation SQL query which you should copy on your migration (I just figured it out from a scheme of mine and could not be 100% correct):

val MIGRATION_1_2: Migration = object : Migration(1, 2) {
    override fun migrate(database: SupportSQLiteDatabase) {
        // Create the new table
        database.execSQL(
            "CREATE TABLE IF NOT EXISTS VehicleDetailsEntityTmp (vehicleId TEXT NOT NULL, updatedOn TEXT, updatedBy TEXT,vehicleClientId INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL )"
        )

        // Copy the data
        database.execSQL(
            "INSERT INTO VehicleDetailsEntityTmp (vehicleId, updatedOn, updatedBy ,vehicleClientId) SELECT vehicleId, updatedOn, updatedBy ,vehicleClientId FROM VehicleDetailsEntity ")

        // Remove the old table
        database.execSQL("DROP TABLE VehicleDetailsEntity")

        // Change the table name to the correct one
        database.execSQL("ALTER TABLE VehicleDetailsEntityTmp RENAME TO VehicleDetailsEntity")
    }
}