Pre-packaged database has an invalid schema Error, when implementing one-to-many in Room

Question

I'm making an Workout Log app.

And while creating a one-to-many relationship to Room DB, I got the following error.

25209-25261 E/AndroidRuntime: FATAL EXCEPTION: DefaultDispatcher-worker-1
    Process: com.example.lightweight, PID: 25209
    java.lang.IllegalStateException: Pre-packaged database has an invalid schema: Workout(com.example.lightweight.data.db.entity.Workout).
     Expected:
    TableInfo{name='Workout', columns={memo=Column{name='memo', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, workoutId=Column{name='workoutId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, title=Column{name='title', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, parentDailyId=Column{name='parentDailyId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[ForeignKey{referenceTable='DailyWorkout', onDelete='CASCADE', onUpdate='NO ACTION', columnNames=[parentDailyId], referenceColumnNames=[dailyId]}], indices=[]}
     Found:
    TableInfo{name='Workout', columns={parentDailyId=Column{name='parentDailyId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=0, defaultValue='null'}, memo=Column{name='memo', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}, workoutId=Column{name='workoutId', type='INTEGER', affinity='3', notNull=true, primaryKeyPosition=1, defaultValue='null'}, title=Column{name='title', type='TEXT', affinity='2', notNull=true, primaryKeyPosition=0, defaultValue='null'}}, foreignKeys=[], indices=[]}

The following image is the current table.

---

DailyWorkout : Recorded by date.

DailyLog
dailyId: Long
date: String
bodyPart: String

Workout : It is a one-to-many relationship with DailyWorkout.

Workout
workoutId: Long
title: String
memo: String
parentDailyId: Long

WorkoutSetInfo : Sets information for each Workout. It is a one-to-many relationship with Workout.

WorkouSetInfo
id: Long
set: Int
weight: String
reps: String
unit: String
parentWorkoutId: Long

---

In Code

DailyWorkout

@Entity
data class DailyWorkout(
    @PrimaryKey(autoGenerate = true)
    val dailyId : Long = 0,
    val date: String,
    val bodyPart: String,
)

Workout

@Entity( 
    foreignKeys = [
        ForeignKey(
            entity = DailyWorkout::class,
            parentColumns = arrayOf("dailyId"), 
            childColumns = arrayOf("parentDailyId"), 
            onDelete = ForeignKey.CASCADE
        )
    ]
)
data class Workout(
    @PrimaryKey(autoGenerate = true)
    var workoutId: Long = 0,
    var title: String = "",
    var memo: String = "",
    val parentDailyId: Long = 0
)

WorkoutSetInfo

@Entity( 
    foreignKeys = [
        ForeignKey(
            entity = Workout::class,
            parentColumns = arrayOf("workoutId"),
            childColumns = arrayOf("parentWorkoutId"), 
            onDelete = ForeignKey.CASCADE 
        )
    ]
)
data class WorkoutSetInfo(
    @PrimaryKey(autoGenerate = true)
    val id: Long = 0,
    val set: Int,
    var weight: String = "0",
    var reps: String = "0",
    var unit: String = "kg",
    val parentWorkoutId: Long = 0
)

Currently, one-to-one of Wokrout and WokroutSetInfo is implemented and works normally.

Error occurs when adding foreignkey= ... to Workout

Answer 1

As the message indicates the schema of the pre-populated database MUST match the schema that room expects. That is what is coded in the @Entity annotated classes.

Rather than trying to establish the difference between the expected (schema according to the @Entity annotated classes) and then changing the schema of the pre-populated database. It is simpler and 100% accurate to:-

1. Compile the project when all the @Entity annotated classes have been finalised (and included in the entities parameter of the @Database annotation).
2. Inspect the java(generated) available via the Android view.
3. Inspect the class that is the same name as the @Database annotated class but suffixed with _Impl
4. Locate the createAllTables method. This will have a statement, for every component (tables, indexes) that executes some SQL. This SQL is exactly what Room expects and can be used as the basis for changing the table(s) in the pre-populated database.

• note ignore the room_master_table (CREATE and INSERT) as Room will create and populate this at run time. It stores a hash of the compiled schema, the hash being used to detect if the schema has been changed (if the compiled hash does not match the stored hash then the schema has been changed)

As an example, to circumvent the issue with the workout table, you could use the following in whatever tool you use (SQlite Studio, DB Beaver or others):-

ALTER TABLE workout RENAME TO original_workout;
CREATE TABLE IF NOT EXISTS `Workout` (`workoutId` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `title` TEXT NOT NULL, `memo` TEXT NOT NULL, `parentDailyId` INTEGER NOT NULL, FOREIGN KEY(`parentDailyId`) REFERENCES `DailyWorkout`(`dailyId`) ON UPDATE NO ACTION ON DELETE CASCADE );
INSERT INTO workout SELECT * FROM original_workout;
DROP TABLE IF EXISTS original_workout;

• WHERE the CREATE TABLE IF NOT EXISTS workout .... is the SQL copied from the generated java.

You may have to use this for other tables (certainly for WorkoutSetInfo as you are also adding foreign keys to this table according to your screen shot).

You then need to close the database, copy it and use the copy to replace the database in the asset folder.

Copying your @Entity annotated classes and then adding:-

@Database(entities = [DailyWorkout::class,Workout::class,WorkoutSetInfo::class], exportSchema = false, version = 1)
abstract class WorkoutDatabase: RoomDatabase() {

}

• added as it is the entities parameter that determines what classes are actually considered as tables and thus determines the generated java.

Then after compiling the project (CTRL + F9) then :-