Home > Blockchain >  FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY) when add data to table room
FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY) when add data to table room

Time:11-19

I have to tables CheckListModel and CheckListPoints, it is one to n relationship, when i try to add data in DB CheckListModel adds correctly, but when code gose to add CheckListPoints i got this error. I have no idea why this happening

This is my DB

@Database(entities = [CheckListModel::class,CheckListPoints::class],version = 4,exportSchema = false)
abstract class CheckListDB : RoomDatabase() {

    abstract fun checkListDBDao():CheckListModelDBDao

    companion object {

        @Volatile
        private var instance: CheckListDB? = null

        fun getInstance(context: Context):CheckListDB{
            return instance ?: synchronized(this){
                instance?: buildDatabase(context).also { instance = it }
            }
        }
        private fun buildDatabase(context: Context): CheckListDB{
            return Room.databaseBuilder(context,CheckListDB::class.java,"check_list_model").fallbackToDestructiveMigration().build()
        }
    }
}

entityes

@Entity(
    tableName = "check_list_point",
    foreignKeys = [
        ForeignKey(entity = CheckListModel::class, parentColumns = ["checkListModelID"],childColumns = ["checkListColumnID"],onDelete = ForeignKey.CASCADE)
    ],
    indices = [Index("checkListColumnID")]
)
data class CheckListPoints(
    @ColumnInfo(name = "correctly")
    var correctly: Boolean,
    @ColumnInfo(name = "requirement")
    var requirement: String,
    @ColumnInfo(name = "passed")
    var passed: Boolean,
    @ColumnInfo(name="checkListColumnID")
    val checkListColumnID: Long,
    @PrimaryKey(autoGenerate = true)
    val checkListPointsModelID: Long = 0L
): Serializable

@Entity(tableName = "check_list_model")
data class CheckListModel (
    @ColumnInfo(name = "check_list_name")
    val checkListName: String,
    @ColumnInfo(name = "check_list_count")
    val checkListCount: Int,
    @ColumnInfo(name = "check_list_result")
    val checkListResult: Int,
    @ColumnInfo(name = "description")
    val description: String,
    @PrimaryKey(autoGenerate = true)
    val checkListModelID: Long = 0L
        ) : Serializable

relationship

data class CheckListWithCheckListModel(
    @Embedded val CheckList: CheckListModel,
    @Relation(
        parentColumn = "checkListModelID",
        entityColumn = "checkListColumnID"
    )
    val checkListPoints: List<CheckListPoints>
)

this is dao

@Dao
interface CheckListModelDBDao {

    @Insert
    fun insertCheckList(data:CheckListModel)

    @Insert
    fun insertCheckListPoint(vararg data:CheckListPoints)

    @Delete
    fun deleteCheckList(checkList: CheckListModel)

    @Transaction
    @Query("SELECT * FROM check_list_model " )
    fun getEverything(): Flow<List<CheckListWithCheckListModel>>
}

and this is how i add

    private var doorCheckListModel = CheckListModel("Дверь",0,0,"4321")
    private val doorCheckListPoint1 = CheckListPoints(false,"1",false,doorCheckListModel.checkListModelID)
    private val doorCheckListPoint2 = CheckListPoints(false,"2",false,doorCheckListModel.checkListModelID)
    private var doorListOfCheckListPoints = listOf<CheckListPoints>(doorCheckListPoint1,doorCheckListPoint2)

    private var windowCheckListModel = CheckListModel("Окно",0,0,"4321")
    private var windowCheckListPoint1 = CheckListPoints(false,"1",false,windowCheckListModel.checkListModelID)
    private var windowCheckListPoint2 = CheckListPoints(false,"1",false,windowCheckListModel.checkListModelID)
    private var windowListOfCheckListPoints = listOf<CheckListPoints>(windowCheckListPoint1,windowCheckListPoint2)

    var checkLists = MutableLiveData<List<CheckListModel>>().apply {
        value = listOf(doorCheckListModel,windowCheckListModel)
    }

    fun addCheckList(name: String){
        viewModelScope.launch(Dispatchers.IO) {
            when (name) {
                "Дверь" -> insert(doorCheckListModel,doorListOfCheckListPoints)
                "Окно" -> insert(windowCheckListModel,windowListOfCheckListPoints)
            }
        }
    }

    private suspend fun insert(checkList: CheckListModel, checkListPoints: List<CheckListPoints>){
        database.insertCheckList(checkList)
        for(checkListPoint in checkListPoints){
            database.insertCheckListPoint(checkListPoint)
        }
    }
}

also i display data from CheckListModel in fragment. CheckListModel added to DB Correctly and display correctly, but CheckListPoints has not

CodePudding user response:

When you create doorCheckListModel, its checkListModelID is initially 0. You use this 0 as checkListColumnID in doorCheckListPoint1. So when you save the CheckListModel, Room automatically generates the primary key and saves in the table. Similar is the case for primary key in CheckListPoints table. But the entries saved in CheckListPoints table still have 0 in checkListColumnID column.

This is why the foreign key constraint is failing. There is no CheckListModel with 0 as its primary key. To fix this, you will have to set the value of checkListColumnID before saving a CheckListPoints entry in the table.

If you go through Room documentation, the @Insert annotated function can optionally return the rowId for the inserted item. For integer primary keys, rowId is the same as primary key.

Try this code:

// Return the primary key here
@Insert
fun insertCheckList(data:CheckListModel): Long
private suspend fun insert(checkList: CheckListModel, checkListPoints: List<CheckListPoints>){
    val id = database.insertCheckList(checkList)
    for(checkListPoint in checkListPoints){
        database.insertCheckListPoint(checkListPoint.copy(checkListColumnID = id))
    }
}
  • Related