Android Room数据库FOREIGN KEY约束失败(code787)问题解决

问题描述 #

使用包含多实体的Room数据库，实体通过外键关联父表，列名及字段配置无误，但插入数据时触发FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)错误，已确认列名无错、字段无缺失。

表实体代码 #

@Entity(
    tableName = "information_data",
    foreignKeys = [
        ForeignKey(
            entity = CollectionData::class,
            parentColumns = ["collection_name"],
            childColumns = ["key_of_linked_collection"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = ImportantInformationData::class,
            parentColumns = ["heading"],
            childColumns = ["key_of_imp_linked_collection"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = ArchivedInformationData::class,
            parentColumns = ["heading"],
            childColumns = ["key_of_archived_linked_collection"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
    ]
)
data class InformationData(
    @PrimaryKey val heading: String,
    val location: String,
    val base_url: String,
    val image_url: String,

    val is_linked_with_collections: Boolean,
    val key_of_linked_collection: String,

    val is_linked_with_imp_collections: Boolean,
    val key_of_imp_linked_collection: String,

    val is_linked_with_archived_collections: Boolean,
    val key_of_archived_linked_collection: String,
)

@Entity(tableName = "collection_data")
data class CollectionData(
    @PrimaryKey val collection_name: String,
    val info_for_saving: String,
)

@Entity(tableName = "archived_information_data")
data class ArchivedInformationData(
    @PrimaryKey val heading: String,
    val location: String,
    val base_url: String,
    val image_url: String,
    val info_for_saving: String,
)

@Entity(tableName = "important_information_data")
data class ImportantInformationData(
    @PrimaryKey var heading: String,
    var location: String,
    var base_url: String,
    var image_url: String,
    var info_for_saving: String,
)

DAO代码 #

• 插入方法：

@Insert
suspend fun addANewInfo(infoData: InformationData)

• 查询方法示例：

@Query("SELECT * FROM information_data WHERE is_linked_with_imp_collections=1 AND key_of_imp_linked_collection=:key")
fun getThisFolderData(key: String): Flow<List<InformationData>>

错误栈 #

android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
                                                                                                        at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
                                                                                                        at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:967)
                                                                                                        at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
                                                                                                        at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:89)
                                                                                                        at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.kt:42)
                                                                                                        at androidx.room.EntityInsertionAdapter.insert(EntityInsertionAdapter.kt:52)
                                                                                                        at com.sample.example.localDB.LocalDBDao_Impl$14.call(LocalDBDao_Impl.java:282)
                                                                                                        at com.sample.example.localDB.LocalDBDao_Impl$14.call(LocalDBDao_Impl.java:277)
                                                                                                        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)
                                                                                                        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1137)
                                                                                                        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:637)
                                                                                                        at java.lang.Thread.run(Thread.java:1012)

解决方案 #

核心原因 #

外键约束默认要求子表关联字段必须对应父表中已存在的主键值，但业务逻辑中存在is_linked_with_xxx的关联标记，说明关联是可选的。当前实体中关联字段被定义为非空String，即使标记为不关联，字段的默认值（或无效值）依然会触发外键检查，导致约束失败。

具体解决步骤 #

1. 将关联字段改为可空类型
   修改InformationData中的三个关联字段，允许其为空：

   data class InformationData(
       @PrimaryKey val heading: String,
       val location: String,
       val base_url: String,
       val image_url: String,
   
       val is_linked_with_collections: Boolean,
       val key_of_linked_collection: String?, // 改为可空
   
       val is_linked_with_imp_collections: Boolean,
       val key_of_imp_linked_collection: String?, // 改为可空
   
       val is_linked_with_archived_collections: Boolean,
       val key_of_archived_linked_collection: String?, // 改为可空
   )
   

2. 匹配关联标记与字段值
   插入数据时严格遵循逻辑：

   • 当is_linked_with_collections为false时，key_of_linked_collection必须设为null
   • 当is_linked_with_collections为true时，必须确保父表collection_data中已存在对应的collection_name值

3. 使用事务处理关联插入（可选）
   如果需要同时插入父表和子表数据，必须用Room的@Transaction注解保证操作原子性，避免插入顺序导致的约束失败：

   @Transaction
   suspend fun insertInfoWithCollection(infoData: InformationData, collectionData: CollectionData) {
       // 先插入父表数据
       addCollection(collectionData)
       // 再插入子表数据
       addANewInfo(infoData)
   }
   

4. 延迟外键约束检查（可选）
   如果业务需要先插入子表再插入父表，可以在外键配置中添加deferred = true，让SQLite延迟约束检查到事务提交时：

   ForeignKey(
       entity = CollectionData::class,
       parentColumns = ["collection_name"],
       childColumns = ["key_of_linked_collection"],
       onDelete = ForeignKey.CASCADE,
       onUpdate = ForeignKey.CASCADE,
       deferred = true // 延迟约束检查
   ),
   

内容的提问来源于stack exchange，提问作者nyefine