Room database rewrites values with the same id

Question

I work with the room database (many-to-many relationship). I try to save the list of cast into a table. But the database sees that I insert cast with the same id (movie id) and just rewrites the value. Any idea how to correct it?

I insert a list with the help of the following query:

   @Insert(onConflict = OnConflictStrategy.REPLACE)
   suspend fun insertCast (cast: List )

But when I try to get the data I get the last data that was inseted.

   @Transaction
   @Query("select * FROM `cast` WHERE id = :id")
   fun getAllCastAssociatedWithMovie(id: Int): List

@Entity(tableName = "movie")
data class MovieDbModel(
   @PrimaryKey(autoGenerate = false)
   val id: Int,
   val poster_path: String,
   val overview: String,
   val title: String)

@Entity(tableName = "cast")
@TypeConverters(CastConverter::class)
data class CastDbModel(
   @PrimaryKey(autoGenerate = false)
   val id : Int,
   val cast: Cast
)

data class Cast(
   val name: String,
   val profile_path: String?,
   val character: String
)
data class MovieWithListOfCast(
   @Embedded /* The parent */
   val movie: CastDbModel,
   @Relation(
       entity = CastDbModel::class, /* The class of the related table(entity) (the children)*/
       parentColumn = "id", /* The column in the @Embedded class (parent) that is referenced/mapped to */
       entityColumn = "id", /* The column in the @Relation class (child) that is referenced (many-many) or references the parent (one(parent)-many(children)) */
       /* For the mapping table */
       associateBy = Junction(
           value = MovieCastCrossRef::class, /* The class of the mapping table */
           parentColumn = "movieIdMap", /* the column in the mapping table that maps/references the parent (@Embedded) */
           entityColumn = "castIdMap" /* the column in the mapping table that maps/references the child (@Relation) */
       )
   )
   val castList: List
)

@Entity(
   tableName = "movie_cast",
   primaryKeys = ["movieIdMap","castIdMap"],
   foreignKeys = [
       /* A MovieId MUST be a value of an existing id column in the movie table */
       ForeignKey(
           entity = MovieDbModel::class,
           parentColumns = ["id"],
           childColumns = ["movieIdMap"],
           /* Optional (helps maintain referential integrity) */
           /* if parent is deleted then children rows of that parent are deleted */
           onDelete = ForeignKey.CASCADE,
           /* if parent column is changed then the column that references the parent is changed to the same value */
           onUpdate = ForeignKey.CASCADE
       ),
       ForeignKey(
           entity = CastDbModel::class,
           parentColumns = ["id"],
           childColumns = ["castIdMap"],
           onDelete = ForeignKey.CASCADE,
           onUpdate = ForeignKey.CASCADE
       )
   ]
)
data class MovieCastCrossRef(
   val movieIdMap: Int,
   @ColumnInfo(index = true)
   val castIdMap: Int
)

MikeT · Answer

Each Cast MUST have a unique id, otherwise a conflict will happen. In your case, if the same id is used, due to the use of OnConflictStrategy.REPLACE the conflict results in the cast being replaced with the data as per the new Cast.

What you should be doing to insert a new Cast, is to insert the new Cast letting the id be generated (by specifying an id of null in the case of AutoGenerate=false), retrieving the generated id e.g. using fun insertCast (cast: List ): LongArray

However, with val id: Int, then you cannot specify null. So you should use var id: Int?=null (same for Movie).

Then when you want to add a Cast to a Movie, you insert a MovieCastCrossreference with the movieIdMap as the id from the respective Movie and with castIdMap as the id from the respective Cast.

With some minor changes to your code:-

@Entity(tableName = "movie")
data class MovieDbModel(
    @PrimaryKey(autoGenerate = false)
    var id: Int?=null, /*<<<<<<<<<< CHANGED to var and default value of null so id can be generated */
    val poster_path: String,
    val overview: String,
    val title: String)

@Entity(tableName = "cast")
@TypeConverters(CastConverter::class)
data class CastDbModel(
    @PrimaryKey(autoGenerate = false)
    var id : Int?=null, /*<<<<<<<<<< CHANGED to var and default value of null so id can be generated */
    val cast: Cast
)

And with an @Dao annotated Interface :-

@Dao
interface AllDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertMovie(movie: List): LongArray
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertCast (cast: List ): LongArray
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertMovieCastXrossRef(movieCastCrossRef: MovieCastCrossRef): Long

    @Transaction
    @Query("") /* Allows the function to have the @Transaction Applied */
    fun insertCastListForASingleMovie(movieId: Int, castList: List) {
        for (castId in castList) {
            insertMovieCastXrossRef(MovieCastCrossRef(movieId,castId))
        }
    }
}

note how the Inserts return a value (LongArray for inserting a List, and just Long for a single insert)

using an @Database annotated class (with .allowMainThredQueries for convenience and brevity) name TheDatabase and the following code in an activity:-

class MainActivity : AppCompatActivity() {
    lateinit var db: TheDatabase
    lateinit var dao: AllDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        db = TheDatabase.getInstance(this)
        dao = db.getAllDao()


        /* Define some Movies */
        val movieList = listOf(
            MovieDbModel(null,"p1","blah1","M1"), /* when run first odds on id will be 1 */
            MovieDbModel(null,"p2","blah2","M2"), /* id likely 2 */
            MovieDbModel(7654,"p3","blah3","M3"), /* id as given i.e 7654 */
            MovieDbModel(null,"p4","blah4","M4"), /* id likely 7655 */
            /* Will very likely NOT BE INSERTED as Movie with id 1 will likely exist */
            MovieDbModel(1,"pOoops","blahOoops","MOoops")
        )
        /* Insert the Movies setting the movieId accordingly from the returned list of inserted id's
            noting that if an insert failed (was ignored i.e. returned id is -1) then a different Log message
         */
        var movieIndex = 0
        for (movieId in dao.insertMovie(movieList)) {
            if (movieId > 0) {
                Log.d("MOVIEINSERT","Movie ${movieList[movieIndex].title} was inserted with an ID of $movieId")

            } else {
                Log.d("MOVIEINSERT","Movie ${movieList[movieIndex].title} insert FAILED due to CONFLICT")
            }
            movieList[movieIndex++].id = movieId.toInt()
        }

        /* Define some CastDBModels */
        val castList = listOf(
            CastDbModel(null,Cast("Fred","fredprofile","fred is fred")),
            CastDbModel(null,Cast("Mary","maryprofile","mary is mary")),
            CastDbModel(null, Cast("Anne","anneprofile","anne is anne")),
            CastDbModel(50124,Cast("Tom","tomprofile","tom is tom")),
            CastDbModel(null,Cast("Bert","bertprofile","bert is bert")),
            CastDbModel(null,Cast("Jane","janeprofile","jane is jane")),
            /* Will very likely NOT BE INSERTED as Movie with id 1 will likely exist */
            CastDbModel(1,Cast("Oooops","NOPROFILE","NOCHARACTER"))
        )
        /* Similar to movies */
        val castIdList = dao.insertCast(castList)
        var castIndex = 0
        val castListToUse: ArrayList = ArrayList()
        for (castId in castIdList) {
            if (castId > 0) {
                Log.d("CASTINSERT","Cast ${castList[castIndex].cast.name} was inserted with an ID of $castId")
                castList[castIndex].id = castId.toInt()
                castListToUse.add(castId.toInt())
            } else {
                Log.d("CASTINSERT","Cast ${castList[castIndex].cast.name} insert Failed")
            }
            castIndex++
        }
        /* Add all the inserted Casts to the 3rd Movie */
        dao.insertCastListForASingleMovie(movieList[3].id!!,castListToUse)
    }
}

The the Log Includes :-

2022-09-09 11:21:52.778 D/MOVIEINSERT: Movie M1 was inserted with an ID of 1
2022-09-09 11:21:52.778 D/MOVIEINSERT: Movie M2 was inserted with an ID of 2
2022-09-09 11:21:52.778 D/MOVIEINSERT: Movie M3 was inserted with an ID of 7654
2022-09-09 11:21:52.778 D/MOVIEINSERT: Movie M4 was inserted with an ID of 7655
2022-09-09 11:21:52.778 D/MOVIEINSERT: Movie MOoops insert FAILED due to CONFLICT

2022-09-09 11:21:52.832 D/CASTINSERT: Cast Fred was inserted with an ID of 1
2022-09-09 11:21:52.833 D/CASTINSERT: Cast Mary was inserted with an ID of 2
2022-09-09 11:21:52.833 D/CASTINSERT: Cast Anne was inserted with an ID of 3
2022-09-09 11:21:52.833 D/CASTINSERT: Cast Tom was inserted with an ID of 50124
2022-09-09 11:21:52.833 D/CASTINSERT: Cast Bert was inserted with an ID of 50125
2022-09-09 11:21:52.833 D/CASTINSERT: Cast Jane was inserted with an ID of 50126
2022-09-09 11:21:52.833 D/CASTINSERT: Cast Oooops insert Failed

This demonstrates that you can insert with a specific id *IF THE SPECIFIC IS IS UNIQUE or with a generated id (AutoGenerate does not in fact force/stop auto generation, it just alters how to get generated id's).

To demonstrate the bonus insert an entire CastList to be related to a single Movie. The movie_cast table ends up being :-

i.e. the 4TH (index 3) movie's id is 7655 so movieIdMap is the same for all rows
and each castIdMap reflects the castId.

Room database rewrites values with the same id

1 Answers