SQLiteConstraintException: FOREIGN KEY constraint failed code 787

huangapple
go评论118阅读模式
英文:

SQLiteConstraintException: FOREIGN KEY constraint failed code 787

问题

I have a menu that can have multiple Sections. I have passed the menuID of a ShopMenuEntity as a foreign key in ShopSectionEntity with the name fk_menu_id.

Both IDs have to be autoGenerated in ShopMenu and ShopSection.

ShopMenuEntity.kt

@Entity(tableName = ShopMenuEntity.TABLE_NAME)
data class ShopMenuEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = COLUMN_ID)
    var menuID: Int = 0,
    var level: Int? = null,
    @ColumnInfo(name = COLUMN_PARENT_ID)
    var parentId: Int = -1,
) {
    companion object {
        const val TABLE_NAME = "shop_menu_table"
        const val COLUMN_ID = "menu_id"
        const val COLUMN_PARENT_ID = "parentId"

        fun mapHttpResponse(subMenu: NewShopMenuResponse, parentId: Int): ShopMenuEntity {
            return ShopMenuEntity(
                // below menuID should be auto incremented
                ///menuID = subMenu.id ?: -1,
                level = subMenu.level,
                parentId = parentId,
            )
        }
    }
}

ShopSectionEntity.kt

@Entity(
    tableName = ShopSectionEntity.TABLE_NAME,
    foreignKeys = [ForeignKey(
        entity = ShopMenuEntity::class,
        parentColumns = [ShopMenuEntity.COLUMN_ID],
        childColumns = [ShopSectionEntity.SHOP_MENU_ID],
        onDelete = ForeignKey.CASCADE
    )],
    indices = [Index(ShopSectionEntity.SHOP_MENU_ID)]
)
data class ShopSectionEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = COLUMN_ID)
    var sectionID: Int = 0,
    @ColumnInfo(name = SHOP_MENU_ID)
    var shopMenuID: Int,
) {

    companion object {

        const val TABLE_NAME = "shop_section_table"
        const val COLUMN_ID = "section_id"
        const val SHOP_MENU_ID = "fk_menu_id"

        fun mapHttpResponse(section: Section, shopMenuID: Int, orderIndex: Int): ShopSectionEntity {
            return ShopSectionEntity(
                // sectionID should be auto incremented
                shopMenuID = shopMenuID,
            )
        }
    }
}

Relation ShopMenuDB.kt

data class ShopMenuDB(
    @field:Embedded
    var shopEntity: ShopMenuEntity,
    @field:Relation(parentColumn = ShopMenuEntity.COLUMN_ID, entityColumn = ShopSectionEntity.SHOP_MENU_ID, entity = ShopSectionEntity::class)
    var sections: List<ShopSectionEntity>,
)

RoomActivity.kt

class RoomActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_room)
        CoroutineScope(Dispatchers.IO).launch {
            readDataFromRaw()?.let {
                addSubMenu(shopMenuDao(), it, -1)
            }
        }
    }

    private fun shopMenuDao() = Room.databaseBuilder(
        applicationContext,
        MatasDatabase::class.java, "matas_database"
    ).build().newShopMenuDao()

    private fun readDataFromRaw(): NewShopMenuResponse? {
        val jsonText = resources.openRawResource(R.raw.respons)
            .bufferedReader().use { it.readText() }
        val moshi: Moshi = Moshi.Builder().build()
        val jsonAdapter = moshi.adapter(
            NewShopMenuResponse::class.java
        )
        val shopResponse = jsonAdapter.fromJson(jsonText)
        return shopResponse
    }

    private suspend fun addSubMenu(
        shopMenuDao: NewShopMenuDao,
        subMenu: NewShopMenuResponse,
        parentID: Int
    ) {
        val dbRootMenus = ArrayList<ShopMenuEntity>()
        val dbSections = ArrayList<ShopSectionEntity>()

        val mSubMenu = ShopMenuEntity.mapHttpResponse(subMenu, parentID)
        dbRootMenus.add(mSubMenu)
        subMenu.sections?.forEachIndexed { sectionIndex, mSection ->
            val sectionEntity =
                ShopSectionEntity.mapHttpResponse(mSection, mSubMenu.menuID, sectionIndex + 1)
            dbSections.add(sectionEntity)
        }
        // shopMenuDao.insertAllLists(dbRootMenus, dbSections)
        // I tried both above and below ways to save menu and sections
        shopMenuDao.insertShopMenu(dbRootMenus[0])
        shopMenuDao.insertSections(dbSections)
    }
}

NewShopMenuDao.kt

@Dao
interface NewShopMenuDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAllLists(
        shopMenuEntityList: List<ShopMenuEntity>,
        shopSectionEntities: List<ShopSectionEntity>?
    )

    @Transaction
    @Query("SELECT * FROM shop_menu_table WHERE shop_menu_table.parentId = :parentId")
    fun getShopMenu(parentId: Int): Flow<ShopMenuDB?>

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertShopMenu(shopMenuEntity: ShopMenuEntity)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertSections(shopSectionEntities: List<ShopSectionEntity>?)

    @Query("SELECT * FROM ${ShopMenuEntity.TABLE_NAME}")
    fun getAll(): List<ShopMenuEntity>

    @Insert
    fun insertAll(vararg shopMenuEntity: ShopMenuEntity)

    @Query("DELETE FROM ${ShopMenuEntity.TABLE_NAME}")
    fun deleteAll()

    @Delete
    fun delete(shopMenuEntity: ShopMenuEntity)
}

I find after debugging that menuId in ShopMenuEntity and sectionID in ShopSectionEntity are not auto-incrementing.

I tried two different approaches to save data but was not successful.

Exception

SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY[787])

Please note that I haven't made any changes to the code itself but only provided the translated content as per your request.

英文:

I have a menu that can have multiple Sections. I have passed the menuID of a ShopMenuEntity as foreign key in ShopSectionEntity with name fk_menu_id.

Both id's have to be autoGenerated in ShopMenu and ShopSection.

ShopMenuEntity.kt

@Entity(tableName = ShopMenuEntity.TABLE_NAME)
data class ShopMenuEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = COLUMN_ID)
    var menuID: Int = 0,
    var level:Int? = null,
    @ColumnInfo(name = COLUMN_PARENT_ID)
    var parentId: Int = -1,
) {
    companion object {
        const val TABLE_NAME = &quot;shop_menu_table&quot;
        const val COLUMN_ID = &quot;menu_id&quot;
        const val COLUMN_PARENT_ID = &quot;parentId&quot;

        fun mapHttpResponse(subMenu: NewShopMenuResponse,parentId: Int): ShopMenuEntity {
            return ShopMenuEntity(
                // below menuID should be auto incremented
                ///menuID = subMenu.id ?: -1,
                level = subMenu.level,
                parentId = parentId,
            )
        }
    }
}

ShopSectionEntity.kt

@Entity(tableName = ShopSectionEntity.TABLE_NAME,
    foreignKeys = [ForeignKey(
        entity = ShopMenuEntity::class,
        parentColumns = arrayOf(ShopMenuEntity.COLUMN_ID),
        childColumns = arrayOf(ShopSectionEntity.SHOP_MENU_ID),
        onDelete = ForeignKey.CASCADE
    )],
    indices = [Index(ShopSectionEntity.SHOP_MENU_ID)])
data class ShopSectionEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = COLUMN_ID)
    var sectionID: Int= 0,
    @ColumnInfo(name = SHOP_MENU_ID)
    var shopMenuID: Int,
) {

    companion object {

        const val TABLE_NAME = &quot;shop_section_table&quot;
        const val COLUMN_ID = &quot;section_id&quot;
        const val SHOP_MENU_ID = &quot;fk_menu_id&quot;

        fun mapHttpResponse(section:Section,shopMenuID:Int,orderIndex:Int):ShopSectionEntity {
            return ShopSectionEntity(
                // sectionID should be auto incremented
                shopMenuID = shopMenuID,

            )
        }
    }
}

Relattion ShopMenuDB.kt

data class ShopMenuDB(
    @field:Embedded
    var shopEntity: ShopMenuEntity,
    @field:Relation(parentColumn = ShopMenuEntity.COLUMN_ID, entityColumn = ShopSectionEntity.SHOP_MENU_ID, entity = ShopSectionEntity::class)
    var sections: List&lt;ShopSectionEntity&gt;,
)

RoomActivity.kt

class RoomActivity : AppCompatActivity() {
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_room)
        CoroutineScope(Dispatchers.IO).launch{
            readDataFromRaw()?.let {
                addSubMenu(shopMenuDao(), it, -1)
            }
        }
    }

    private fun shopMenuDao() = Room.databaseBuilder(
        applicationContext,
        MatasDatabase::class.java, &quot;matas_database&quot;
    ).build().newShopMenuDao()

    private fun readDataFromRaw():NewShopMenuResponse?{
        val jsonText = resources.openRawResource(R.raw.respons)
            .bufferedReader().use { it.readText() }
        val moshi: Moshi = Moshi.Builder().build()
        val jsonAdapter = moshi.adapter(
            NewShopMenuResponse::class.java
        )
        val shopResponse = jsonAdapter.fromJson(jsonText)
        return shopResponse
    }

    private suspend fun addSubMenu(
        shopMenuDao: NewShopMenuDao,
        subMenu: NewShopMenuResponse,
        parentID: Int
    ) {
        val dbRootMenus = ArrayList&lt;ShopMenuEntity&gt;()
        val dbSections = ArrayList&lt;ShopSectionEntity&gt;()

        val mSubMenu = ShopMenuEntity.mapHttpResponse(subMenu, parentID)
        dbRootMenus.add(mSubMenu)
        subMenu.sections?.forEachIndexed { sectionIndex, mSection -&gt;
            val sectionEntity = ShopSectionEntity.mapHttpResponse(mSection, mSubMenu.menuID,sectionIndex + 1)
            dbSections.add(sectionEntity)
        }
        ///shopMenuDao.insertAllLists(dbRootMenus, dbSections)
        // I tried both above and below way to save menu and sections
        shopMenuDao.insertShopMenu(dbRootMenus[0])
        shopMenuDao.insertSections(dbSections)
    }
}

NewShopMenuDao.kt

@Dao
interface NewShopMenuDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAllLists(shopMenuEntityList: List&lt;ShopMenuEntity&gt;, shopSectionEntities: List&lt;ShopSectionEntity&gt;?)
 
    @Transaction
    @Query(&quot;SELECT * FROM shop_menu_table WHERE shop_menu_table.parentId = :parentId&quot;)
    fun getShopMenu(parentId:Int): Flow&lt;ShopMenuDB?&gt;

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertShopMenu(shopMenuEntity: ShopMenuEntity)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertSections(shopSectionEntities: List&lt;ShopSectionEntity&gt;?)

    @Query(&quot;SELECT * FROM ${ShopMenuEntity.TABLE_NAME}&quot;)
    fun getAll(): List&lt;ShopMenuEntity&gt;

    @Insert
    fun insertAll(vararg shopMenuEntity: ShopMenuEntity)

    @Query(&quot;DELETE FROM ${ShopMenuEntity.TABLE_NAME}&quot;)
    fun deleteAll()

    @Delete
    fun delete(shopMenuEntity: ShopMenuEntity)
}

I find after debug that menuId in ShopMenuEntity and sectionID in ShopSectionEntity is not auto incrementing.

I tried two different approaches to save data but was not successful.

Exception

SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY[787])
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:1127)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.kt:42)
at androidx.room.EntityInsertionAdapter.insert(EntityInsertionAdapter.kt:85)
at matas.matas.core.data.db.dao.NewShopMenuDao_Impl$8.call(NewShopMenuDao_Impl.java:164)
at matas.matas.core.data.db.dao.NewShopMenuDao_Impl$8.call(NewShopMenuDao_Impl.java:159)
at androidx.room.CoroutinesRoom$Companion$execute$2.invokeSuspend(CoroutinesRoom.kt:65)
at kotlin.coroutines.jvm.internal.BaseContinuationImpl.resumeWith(ContinuationImpl.kt:33)
at kotlinx.coroutines.DispatchedTask.run(DispatchedTask.kt:106)
at androidx.room.TransactionExecutor.execute$lambda$1$lambda$0(TransactionExecutor.kt:36)
at androidx.room.TransactionExecutor.$r8$lambda$AympDHYBb78s7_N_9gRsXF0sHiw(Unknown Source:0)
at androidx.room.TransactionExecutor$$ExternalSyntheticLambda0.run(Unknown Source:4)

答案1

得分: 1

> Both id's have to be autoGenerated in ShopMenu and ShopSection.
> 在ShopMenu和ShopSection中，两个id必须是自动生成的。

  • They in fact don't BUT that doesn't matter (the demo below includes examples)
  • 事实上，它们并不是自动生成的，但这并不重要（下面的演示包括示例）。

> I have also find after debug that menuId in ShopMenuEntity and sectionID in ShopSectionEntity not auto incrementing as well.
> 经过调试后，我还发现ShopMenuEntity中的menuId和ShopSectionEntity中的sectionID也没有自动递增。

The values are generated as the demo below shows. I believe your issue is an expectation that storing a value in the database will be reflected back to the objects.
这些值是根据下面的演示生成的。我认为您的问题是您期望将值存储在数据库中后会反映到对象中。

The fix is to utilise the values returned when inserting the data.
修复方法是在插入数据时利用返回的值。

> FOREIGN KEY constraint failed code 787
> 外键约束失败，代码787

This is probably due to some expectation beyond what actually can be used. e.g. as per the previous answer, you appear to be expecting objects to be updated with the respective id when the data is stored in the database.
这可能是因为某种超出实际可用范围的期望。例如，根据先前的答案，您似乎期望在将数据存储在数据库中时更新对象的相应ID。

When an alias of the rowid is generated (for autoGenerate=true the column MUST be an alias of the rowid), the value is generated by SQLite and stored. It will only be available if it is extracted from the database.
当生成rowid的别名时（对于autoGenerate=true，列必须是rowid的别名），该值由SQLite生成并存储。只有在从数据库中提取时才可用。

Fortunately the @Insert annotated functions do cater for retrieval of the value and returns it as a Long (if inserting a single object) or as an array of Longs when inserting an array/list of objects.
幸运的是，@Insert 注释的函数确实支持检索该值，并在插入单个对象时返回它作为Long，或在插入数组/对象列表时返回Long数组。

  • If the value was not inserted for a limited set of reasons (conflicts) then the returned value will be -1. AS Foreign Key exception is NOT one of those reasons.
  • 如果由于一些有限的原因（冲突）未插入该值，则返回的值将为-1。外键异常不是这些原因之一。

When you introduce a Foreign Key, in Room via foreignKeys parameter of the @Entity annotation. You are introducing a constraint (rule) that says that the value of the child column MUST be a value that exists in the parent column of one of the rows in the parent table. That is it is designed to ensure referential integrity (that there are no orphans (children without parents)). As such breaking the rule is unacceptable and hence why an exception as opposed to a controllable/catchable type error.
当您在Room中通过@Entity注释的foreignKeys参数引入外键时，您引入了一个约束（规则），该规则指定子列的值必须是父表中的一行的父列中存在的值。这是为了确保引用完整性（即没有孤儿（没有父母的子女））。因此，违反规则是不可接受的，因此会引发异常，而不是可控/可捕获的类型错误。

As such it is IMPERATIVE that the specified value of the parent column is provided and is one that exists.
因此，非常重要的是提供父列的指定值，并且该值存在。

In your case the generated menuId of a ShopMenuEntity is required to enable a ShopSectionEntity to be inserted.
在您的情况下，需要生成的ShopMenuEntity的menuId以允许插入ShopSectionEntity。

The following demonstrates the above
以下演示了上述内容

Your ShopMenuEntity, ShopSectionEntity and ShopMenuDB have been used asis bar the removal of the companion functions and are:-
您的ShopMenuEntity、ShopSectionEntity和ShopMenuDB已按原样使用，除了移除伴随函数外，它们分别是：-

@Entity(tableName = ShopMenuEntity.TABLE_NAME)
data class ShopMenuEntity(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = COLUMN_ID)
var menuID: Int = 0,
var level: Int? = null,
@ColumnInfo(name = COLUMN_PARENT_ID)
var parentId: Int = -1,
) {
companion object {
const val TABLE_NAME = "shop_menu_table"
const val COLUMN_ID = "menu_id"
const val COLUMN_PARENT_ID = "parentId"
}
}

@Entity(tableName = ShopSectionEntity.TABLE_NAME,
foreignKeys = [ForeignKey(
entity = ShopMenuEntity::class,
parentColumns = arrayOf(ShopMenuEntity.COLUMN_ID),
childColumns = arrayOf(ShopSectionEntity.SHOP_MENU_ID),
onDelete = ForeignKey.CASCADE
)],
indices = [Index(ShopSectionEntity.SHOP_MENU_ID)])
data class ShopSectionEntity(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = COLUMN_ID)
var sectionID: Int = 0,
@ColumnInfo(name = SHOP_MENU_ID)
var shopMenuID: Int,
) {

companion object {

    const val TABLE_NAME = "shop_section_table"
    const val COLUMN_ID = "section_id"
    const val SHOP_MENU_ID = "fk_menu_id"
}

}

data class ShopMenuDB(
@field:Embedded
var shopEntity: ShopMenuEntity,
@field:Relation(parentColumn = ShopMenuEntity.COLUMN_ID, entityColumn = ShopSectionEntity.SHOP_MENU_ID, entity = ShopSectionEntity::class)
var sections: List,
)

The NewShopMenuDao interface has been altered to:-
NewShopMenuDao接口已经被更改为：-

  1. allow the code to be run on the main thread for the demo
  2. 允许在演示中在主线程上运行代码
  3. return the menuId when a ShopMenuEntity is inserted
  4. 在插
英文:

> Both id's have to be autoGenerated in ShopMenu and ShopSection.

  • They in fact don't BUT that doesn't matter (the demo below includes examples)

> I have also find after debug that menuId in ShopMenuEntity and sectionID in ShopSectionEntity not auto incrementing as well.

The values are generated as the demo below shows. I believe your issue is an expectation that storing a value in the database will be reflected back to the objects.

The fix is to utilise the values returned when inserting the data.

> FOREIGN KEY constraint failed code 787

This is probably due to some expectation beyond what actually can be used. e.g. as per the previous answer, you appear to be expecting objects to be updated with the respective id when the data is stored in the database.

When an alias of the rowid is generated (for autoGenerate=true the column MUST be an alias of the rowid), the value is generated by SQLite and stored. It will only be available if it is extracted from the database.

Fortunately the @Insert aonnotated functions do cater for retrieval of the value and returns it as a Long (if inserting a single object) or as an array of Longs when inserting an array/list of objects.

  • If the value was not inserted for a limited set of reasons (conflicts) then the returned value will be -1. AS Foreign Key exception is NOT one of those reasons.

When you introduce a Foreign Key, in Room via foreignKeys parameter of the @Entity annotation. You are introducing a constraint (rule) that says that the value of the child column MUST be a value that exists in the parent column of one of the rows in the parent table. That is it is designed to ensure referential integrity (that there are no orphans (children without parents)). As such breaking the rule is unacceptable and hence why an exception as opposed to a controllable/catchable type error.

As such it is IMPERATIVE that the specified value of the parent column is provided and is one that exists.

In your case the generated menuId of a ShopMenuEntity is required to enable a ShopSectionEntity to be inserted.

The following demonstrates the above

Your ShopMenuEntity, ShopSectionEntity and ShopMenuDB have been used asis bar the removal of the companion functions and are:-

@Entity(tableName = ShopMenuEntity.TABLE_NAME)
data class ShopMenuEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = COLUMN_ID)
    var menuID: Int = 0,
    var level:Int? = null,
    @ColumnInfo(name = COLUMN_PARENT_ID)
    var parentId: Int = -1,
) {
    companion object {
        const val TABLE_NAME = &quot;shop_menu_table&quot;
        const val COLUMN_ID = &quot;menu_id&quot;
        const val COLUMN_PARENT_ID = &quot;parentId&quot;

        /*
        fun mapHttpResponse(subMenu: NewShopMenuResponse,parentId: Int): ShopMenuEntity {
            return ShopMenuEntity(
                // below menuID should be auto incremented
                ///menuID = subMenu.id ?: -1,
                level = subMenu.level,
                parentId = parentId,
            )
        }
         */
    }
}

@Entity(tableName = ShopSectionEntity.TABLE_NAME,
    foreignKeys = [ForeignKey(
        entity = ShopMenuEntity::class,
        parentColumns = arrayOf(ShopMenuEntity.COLUMN_ID),
        childColumns = arrayOf(ShopSectionEntity.SHOP_MENU_ID),
        onDelete = ForeignKey.CASCADE
    )],
    indices = [Index(ShopSectionEntity.SHOP_MENU_ID)])
data class ShopSectionEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = COLUMN_ID)
    var sectionID: Int= 0,
    @ColumnInfo(name = SHOP_MENU_ID)
    var shopMenuID: Int,
) {

    companion object {

        const val TABLE_NAME = &quot;shop_section_table&quot;
        const val COLUMN_ID = &quot;section_id&quot;
        const val SHOP_MENU_ID = &quot;fk_menu_id&quot;

        /*
        fun mapHttpResponse(section:Section,shopMenuID:Int,orderIndex:Int):ShopSectionEntity {
            return ShopSectionEntity(
                // sectionID should be auto incremented
                shopMenuID = shopMenuID,
                )
        }
         */
    }
}

data class ShopMenuDB(
    @field:Embedded
    var shopEntity: ShopMenuEntity,
    @field:Relation(parentColumn = ShopMenuEntity.COLUMN_ID, entityColumn = ShopSectionEntity.SHOP_MENU_ID, entity = ShopSectionEntity::class)
    var sections: List&lt;ShopSectionEntity&gt;,
)

The NewShopMenuDao interface has been altered to:-

  1. allow the code to be run on the main thread for the demo
  2. return the menuId when a ShopMenuEntity is inserted
  3. add a query to extract All ShopMenuEntity's with the related list of ShopSectinEntity's.

As per:-

@Dao
interface NewShopMenuDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    /*suspend*/ fun insertAllLists(shopMenuEntityList: List&lt;ShopMenuEntity&gt;, shopSectionEntities: List&lt;ShopSectionEntity&gt;?)

    @Transaction
    @Query(&quot;SELECT * FROM shop_menu_table WHERE shop_menu_table.parentId = :parentId&quot;)
    fun getShopMenu(parentId:Int): /*Flow*/List&lt;ShopMenuDB?&gt;

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    /*suspend*/ fun insertShopMenu(shopMenuEntity: ShopMenuEntity): Long /*&lt;&lt;&lt;&lt;&lt;&lt;&lt;&lt; ADDED so the menuId (generated id) is returned */

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    /*suspend*/ fun insertSections(shopSectionEntities: List&lt;ShopSectionEntity&gt;?)

    @Query(&quot;SELECT * FROM ${ShopMenuEntity.TABLE_NAME}&quot;)
    fun getAll(): List&lt;ShopMenuEntity&gt;

    @Insert
    fun insertAll(vararg shopMenuEntity: ShopMenuEntity)

    @Query(&quot;DELETE FROM ${ShopMenuEntity.TABLE_NAME}&quot;)
    fun deleteAll()

    @Delete
    fun delete(shopMenuEntity: ShopMenuEntity)

    @Transaction
    @Query(&quot;SELECT * FROM ${ShopMenuEntity.TABLE_NAME}&quot;)
    fun getAllShopMenusWithRelatedShopSections(): List&lt;ShopMenuDB&gt;
}

An @Database annotated abstract class has been added, as one is needed. it being:-

@Database(entities = [ShopMenuEntity::class,ShopSectionEntity::class], exportSchema = false, version = 1)
abstract class MatasDatabase: RoomDatabase() {
    abstract fun getNewShopMenuDao(): NewShopMenuDao

    companion object {
        private var instance: MatasDatabase?=null
        fun getInstance(context: Context): MatasDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,MatasDatabase::class.java,&quot;matas_database.db&quot;)
                    .allowMainThreadQueries() /* for brevity of demo */
                    .build()
            }
            return instance as MatasDatabase
        }
    }
}

To actually demonstrate that your code basically works; that it does generate values and that the foreign keys can be used. The following activity code was used:-

const val TAG = &quot;DBINFO&quot;
class MainActivity : AppCompatActivity() {

    lateinit var db: MatasDatabase
    lateinit var dao: NewShopMenuDao
    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)

        db = MatasDatabase.getInstance(this)
        dao = db.getNewShopMenuDao()


        /* Add a shop getting the generated menuId */
        /* menuId and parentId not given so default values (0 for menuId and -1 for parentId) */
        val firstshopmenuId = dao.insertShopMenu(ShopMenuEntity(level=100))
        /* menuId specified as 300 level and parentId default (null for level and -1 for parentId) */
        val secondShopMenuId = dao.insertShopMenu(ShopMenuEntity(300))
        /* all defaults */
        val thirdShopMenuId = dao.insertShopMenu(ShopMenuEntity())

        Log.d(TAG,&quot;After adding the shops the menuId values are \n\t${firstshopmenuId},\n\t${secondShopMenuId}, and\n\t${thirdShopMenuId}&quot;)

        dao.insertSections(
            listOf
                (
                ShopSectionEntity(shopMenuID = firstshopmenuId.toInt()),
                ShopSectionEntity(sectionID = 500,firstshopmenuId.toInt()),
                ShopSectionEntity(shopMenuID = secondShopMenuId.toInt()),
                ShopSectionEntity(shopMenuID = firstshopmenuId.toInt()),
                ShopSectionEntity(shopMenuID = thirdShopMenuId.toInt()),
                ShopSectionEntity(shopMenuID = secondShopMenuId.toInt())
            )
        )
        try {
            dao.insertSections(listOf(ShopSectionEntity(shopMenuID = 9999)))
        } catch (e: Exception) {
            Log.d(TAG,&quot;Ouch Something went wrong!!!! Exception was\n${e.printStackTrace()}&quot;)
        }

        for (smwss in dao.getAllShopMenusWithRelatedShopSections()) {
            val sb = StringBuilder()
            for (ss in smwss.sections) {
                sb.append(&quot;\n\tSectionID is ${ss.sectionID} referenced ShopMenu ID is ${ss.shopMenuID}&quot;)
            }
            Log.d(TAG,&quot;SHopMenu MENUID is ${smwss.shopEntity.menuID} &quot; +
                    &quot;LEVEL is ${smwss.shopEntity.level} &quot; +
                    &quot;PARENT ID is ${smwss.shopEntity.parentId}. &quot; +
                    &quot;It has ${smwss.sections.size} sections. They are:- $sb&quot;)
        }
    }
}

RESULT / CONCLUSION

When run then the log first of all includes:-

2023-08-06 08:14:24.554 D/DBINFO: After adding the shops the menuId values are 
    	1,
    	300, and
    	301
  • This shows that the first and third menuId values were generated according to the values returned from the insert.
  • It also demonstrates that if a non-zero value (300 in the example) that you can in fact specify a value and that has been used.
    • as the value was greater than 1 (the first insertion) then generated values will be greater and hence 301 for the 3rd insert.

The log then includes:-

2023-08-06 08:14:24.575 W/System.err: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
2023-08-06 08:14:24.575 W/System.err:     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
2023-08-06 08:14:24.575 W/System.err:     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:796)
2023-08-06 08:14:24.575 W/System.err:     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
2023-08-06 08:14:24.575 W/System.err:     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
2023-08-06 08:14:24.575 W/System.err:     at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.kt:42)
2023-08-06 08:14:24.576 W/System.err:     at androidx.room.EntityInsertionAdapter.insert(EntityInsertionAdapter.kt:85)
2023-08-06 08:14:24.576 W/System.err:     at a.a.so76840478kotlinroomforeignkeys.NewShopMenuDao_Impl.insertSections(NewShopMenuDao_Impl.java:138)
2023-08-06 08:14:24.576 W/System.err:     at a.a.so76840478kotlinroomforeignkeys.MainActivity.onCreate(MainActivity.kt:44)
2023-08-06 08:14:24.576 W/System.err:     at android.app.Activity.performCreate(Activity.java:7136)
2023-08-06 08:14:24.576 W/System.err:     at android.app.Activity.performCreate(Activity.java:7127)
2023-08-06 08:14:24.577 W/System.err:     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1271)
2023-08-06 08:14:24.577 W/System.err:     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2893)
2023-08-06 08:14:24.577 W/System.err:     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3048)
2023-08-06 08:14:24.578 W/System.err:     at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:78)
2023-08-06 08:14:24.578 W/System.err:     at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:108)
2023-08-06 08:14:24.578 W/System.err:     at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:68)
2023-08-06 08:14:24.579 W/System.err:     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1808)
2023-08-06 08:14:24.579 W/System.err:     at android.os.Handler.dispatchMessage(Handler.java:106)
2023-08-06 08:14:24.580 W/System.err:     at android.os.Looper.loop(Looper.java:193)
2023-08-06 08:14:24.581 W/System.err:     at android.app.ActivityThread.main(ActivityThread.java:6669)
2023-08-06 08:14:24.581 W/System.err:     at java.lang.reflect.Method.invoke(Native Method)
2023-08-06 08:14:24.581 W/System.err:     at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:493)
2023-08-06 08:14:24.581 W/System.err:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:858)
2023-08-06 08:14:24.582 D/DBINFO: Ouch Something went wrong!!!! Exception was

That is the purposeful FKEY violation, due to trying to reference the non-existent ShopMenuEntity that has a menuId of 9999.

However, the Log then includes, the extracted ShopMenuDB objects via the getAllShopMenusWithRelatedShopSections function as per:-

2023-08-06 08:14:24.590 D/DBINFO: SHopMenu MENUID is 1 LEVEL is 100 PARENT ID is -1. It has 3 sections. They are:- 
    	SectionID is 1 referenced ShopMenu ID is 1
    	SectionID is 500 referenced ShopMenu ID is 1
    	SectionID is 502 referenced ShopMenu ID is 1
2023-08-06 08:14:24.590 D/DBINFO: SHopMenu MENUID is 300 LEVEL is null PARENT ID is -1. It has 2 sections. They are:- 
    	SectionID is 501 referenced ShopMenu ID is 300
    	SectionID is 504 referenced ShopMenu ID is 300
2023-08-06 08:14:24.590 D/DBINFO: SHopMenu MENUID is 301 LEVEL is null PARENT ID is -1. It has 1 sections. They are:- 
    	SectionID is 503 referenced ShopMenu ID is 301

That is:-

  1. The ShopMenuEntity's all have the expected related ShopSectionEntity children
  2. That the SectionId of the ShopSectionEntity's have both been generated (i.e. 1, 501,502, 503 and 504) and specified (i.e. 500).

huangapple
  • 本文由 发表于 2023年8月5日 14:57:05
  • 转载请务必保留本文链接：https://go.coder-hub.com/76840478.html
  • android
  • android-room
  • foreign-keys
  • kotlin
  • relational-database
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定