how to handle FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY) in room db - android

I'm dealing with a room DB that has multiple entities, which are linked with a parent table using foreign keys with appropriate column names, respectively. The main purpose I've used foreign keys for here is to link respective data in the parent table with child tables.

But the problem is that I'm getting FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY). As far as I've researched this, this error gets triggered when there's a wrong naming (in column names for parent or child) or missing column names in the respective parent or child table, but in my case there's nothing like this;

Tables:

@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

For inserting data:-

 @Insert
    suspend fun addANewInfo(infoData: InformationData)

if i want to retrieve any sort of data, i just use queries:

@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>>

When i try to insert data into these with appropriate data, it throws error like this:

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)

I've seen related questions answers already, but they don't answer in wrt my specific case; How can i fix this? Thank you :))

Solution

I believe that your issue may be the expectation that an InformationData may or may not have a related(linked) parent (CollectionData and /or ArchivedInformationData and/or ImportantInformationData)

That is in addition to the link/relation you have an "is_linked" indicator/flag.

The main purpose I've used foreign keys for here is to link respective data in the parent table with child tables.

A Foreign Key does not define a link (relation), instead it defines a constraint (rule) that says that the parent of the child MUST exist. That is it supports relationships by enforcing what is termed as referential integrity.

As such the rule will NOT ALLOW a non-existent parent. That is an InformationData MUST have:-

a value in the key_of_linked_collection field that is an existing parent (row in the collection_data table)
a value in the key_of_imp_linked_collection field that is an existing parent (row in the important_information_data table)
a value in the key_of_archived_linked_collection field that is an existing parent (row in the archived_information_data)

As such a not linked situation breaks one of the 3 rules and the resultant FOREIGN KEY constraint failed.

If you want the option of parents not existing, then you will have to remove the ForeignKey definitions. The link will still exist when the parent does exist. However, you would need to cater for the non-existent parent.

Perhaps the following version of the InformationData class would suit:-

@Entity(
    tableName = "information_data"
    /* COMMENTED OUT FKEY DEFINITIONS ,
    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?, /* CHANGED TO ALLOW NULL */

    val is_linked_with_imp_collections: Boolean,
    val key_of_imp_linked_collection: String?,  /* CHANGED TO ALLOW NULL */

    val is_linked_with_archived_collections: Boolean,
    val key_of_archived_linked_collection: String?,  /* CHANGED TO ALLOW NULL */
)

NOTE see the comments

Additional

how to handle FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY) in room db - android

Here's an example based upon your code that handles such exceptions (which may or may not suit). The demo also demonstrates the suggested changes to suit what you appear to be trying to undertake.

So first your original InformationData class is as per your code. To demonstrate the suggested there is an additional @Entity annotated class AltInformationData as per:-

@Entity
data class AltInformationData(
    @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?,
)

To demonstrate the handling of Foreign Key Constraint conflicts there is a function in the @Dao annotated interface namely insertInformationDataIfFKeysOK.

This function actually demonstrate 2 potential ways of handling the conflicts namely:-

a) testing that the specified parent's exist and if not then bypassing the insert.
b) trapping the exception

To perform the tests the function:-

invokes queries to see if the specified parents exist using @Query annotated functions to see if the respective parent exists
is run as a single transaction and thus is annotated with @Transaction and also an empty @Query (to make Room think that it can be enclosed in a transaction).

To demonstrate with and without valid parents and the alternative (without foreign keys) then there is an equivalent function (that takes an InformationData but inserts an AltInformationData). This function is insertAltInformation

In addition the @Dao annotated interface has functions to allow the insertion into the other tables. As such the AllDAOs interface, in it's entirety, is:-

@Dao
interface AllDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(collectionData: CollectionData): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(importantInformationData: ImportantInformationData): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(archivedInformationData: ArchivedInformationData): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(informationData: InformationData): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(altInformationData: AltInformationData): Long

    @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>/*>*/

    @Query("SELECT count(*) FROM important_information_data WHERE heading=:heading")
    fun doesIIDExist(heading: String?): Int
    @Query("SELECT count(*) FROM collection_data WHERE collection_name=:collectionName")
    fun doesCDExist(collectionName: String?): Int
    @Query("SELECT count(*) FROM archived_information_data WHERE heading=:heading")
    fun doesAIDExist(heading: String?): Int



    @Transaction
    @Query("")
    fun insertInformationDataIfFKeysOK(informationData: InformationData): Int {
        var rv: Int = 0
        try {
            if (doesIIDExist(informationData.key_of_imp_linked_collection) < 1) rv += -1
            if (doesAIDExist(informationData.key_of_archived_linked_collection) < 1) rv += -10
            if (doesCDExist(informationData.key_of_linked_collection) < 1) rv += -100
            if (rv==0) {
                if (insert(informationData) < 1) rv += -1000
            }
        } catch (e: Exception) {
            /* Should not happen as check exists (3 of them) should result in skipping insert */
            /* i.e. catch could be an alternative approach to handling the FKEY conflict */
            Log.e("NOT_INSERTED","ROW NOT INSERTED due to Constraint conflict\n\t${e.message}")
        }
        if (rv < 0) {
            Log.e("INSERT_RESULT","Row was not inserted result code was ${rv} where:-" +
                    "\n\t -1 indicates that important key was not a parent," +
                    "\n\t -10 indicates that archived key was not a parent," +
                    "\n\t -100 indicates that collection key was not a parent, and " +
                    "\n\t -1000 indicates that the actual insert failed (see logged message)." +
                    "\n\t the result might be a combination e.g. -111 indicates that all three keys were not a parent.")
        } else {
            Log.e("INSERT_RESULT","Row was successfully inserted.")
        }
        return rv
    }
    @Query("")
    fun insertAltInformation(informationData: InformationData): Long {
        var rv = insert(AltInformationData(
            heading =informationData.heading,
            location = informationData.location,
            base_url = informationData.base_url,
            image_url = informationData.image_url,
            is_linked_with_collections =  informationData.is_linked_with_collections,
            key_of_linked_collection =  informationData.key_of_linked_collection,
            is_linked_with_archived_collections =  informationData.is_linked_with_archived_collections,
            key_of_archived_linked_collection = informationData.key_of_archived_linked_collection,
            is_linked_with_imp_collections =  informationData.is_linked_with_imp_collections,
            key_of_imp_linked_collection = informationData.key_of_imp_linked_collection
        ))
        if (rv < 0) {
            Log.e("ALTINSERT_RESULT","Alt Row was not inserted!!!!")
        } else {
            Log.d("ALTINSERT_RESULT","Alt Row was inserted")
        }
        return rv
    }
}

note that as the demo is run on the main thread for brevity, that the `Flow~ have been commented out.

The @Database annotated abstract class used:-

@Database(entities = [CollectionData::class,ImportantInformationData::class,ArchivedInformationData::class,InformationData::class,AltInformationData::class], version = 1, exportSchema = false)
abstract class TheDatabase: RoomDatabase() {
    abstract fun getAllDAOs(): AllDAOs
    companion object {
        private var instance: TheDatabase?=null
        fun getInstance(context: Context): TheDatabase {
            if (instance==null) {
                instance = Room.databaseBuilder(context,TheDatabase::class.java,"the_database.db")
                    .allowMainThreadQueries()
                    .build()
            }
            return instance as TheDatabase
        }
    }
}

Finally, to demonstrate, some activity code:-

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

        db = TheDatabase.getInstance(this)
        dao = db.getAllDAOs()

        val cd1 = CollectionData("CD01","cd01blah")
        val ai1 = ArchivedInformationData("AI01","here","ai01url","ai01.jpg","ai01blah")
        val iid1 = ImportantInformationData("IID01","there","iid01url","iid01.jpg","iid01blah")

        /* ATTEMPT 1 - insert where a FKEY conflict would happen */
        dao.insertInformationDataIfFKeysOK(InformationData("ID01","wherever","id01url","id01.jpg",
            true,"rubbish",
            false,"rubbish",
            true,null)
        )

        /* ATTEMPT 2 - insert the alternative where no FKEY constraints apply */
        dao.insertAltInformation(InformationData("ID01","wherever","id01url","id01.jpg",
            true,"rubbish",
            false,"rubbish",
            true,null)
        )

        /* Add some valid parents */
        dao.insert(cd1)
        dao.insert(ai1)
        dao.insert(iid1)

        /* prepare the InformationData for insertion */
        val id2 = InformationData(
            "ID02", location = "somewhere", base_url = "id02url", image_url = "id02.jpg",
            is_linked_with_imp_collections = true, key_of_imp_linked_collection = iid1.heading,
            is_linked_with_collections = true, key_of_linked_collection = cd1.collection_name,
            is_linked_with_archived_collections = true, key_of_archived_linked_collection = ai1.heading
        )
        /* ATTEMPT 3 - insert with valid (non conflicting) data */
        dao.insertInformationDataIfFKeysOK(id2)
        /* ATTEMPT 4 - apply to the alternative */
        dao.insertAltInformation(id2)
    }
}

note the above is designed purely as a one off (run once only) demo.

RESULTS

Attempt 1

This would normally fail with an FKEY conflict but due to the checking the insert is bypassed. The resultant portion of the log:-

2023-06-28 13:21:49.807 E/INSERT_RESULT: Row was not inserted result code was -111 where:-
         -1 indicates that important key was not a parent,
         -10 indicates that archived key was not a parent,
         -100 indicates that collection key was not a parent, and 
         -1000 indicates that the actual insert failed (see logged message).
         the result might be a combination e.g. -111 indicates that all three keys were not a parent.

i.e. the -111 indicates that none of the parents existed (-1 for the imp parent (rubbish) not existing, + -10 for the archived parent (rubbish) not existing and + -100 for the collection parent (rubbish not existing)) -- in fact NO ROW exists in any of the 3 parent tables.

Attempt 2

This works as there are no FKEY constraints so rubbish is fine as a value (except that obviously it would not link to anything unless rows were subsequently added i.e. as far as relationships are concerned the inserted row is a 3 times orphan). The log says:-

2023-06-28 13:21:49.810 D/ALTINSERT_RESULT: Alt Row was inserted

Attempt 3

This works, the parents now exist. The log:-

2023-06-28 13:21:49.843 E/INSERT_RESULT: Row was successfully inserted.

Attempt 4

This works. The log:-

2023-06-28 13:21:49.844 D/ALTINSERT_RESULT: Alt Row was inserted

The database (via App Inspection) contains the following:-

information_data table:-

i.e. only the single row of the 2 attempts to insert

altinformationaata table :-

i.e. both rows exist, even though the first row has no valid links.

As far as "how to retrieve linked data" the following query demonstrates a typical way to get the informationdata with it's parent data (again using AppInspection):-

And for the AltInformationData:-

noting that for both only the rows with valid linked data is retrieved (i.e. just the single row) even though there are two rows in the altinformationdata table.