Foreign Key constraint failed using Room

Question

I've looked at this code every which way. Searched here for a solution. I discovered that autogenerated primary key field can't be used as a parent column Stack question here. However I have used it in java, but it doesn't seem to be working in Kotlin (which I'm trying to learn).

Here are my classes:

User.class

@Entity
@Parcelize
data class User(
    @PrimaryKey(autoGenerate = true) val id: Long,
    val email: String,
    val name: String,
    val defaultRecipient: String
) : Parcelable

===

University.class

@Parcelize
@Entity(
    foreignKeys = [ForeignKey(
        entity = User::class,
        parentColumns = ["id"],
        childColumns = ["userId"],
        onDelete = ForeignKey.CASCADE,
        onUpdate = ForeignKey.CASCADE
    )], indices = [Index(value = ["userId"])]
)
data class University(
    val userId: Long,
    @PrimaryKey @NonNull val name: String

) : Parcelable {
    override fun equals(other: Any?): Boolean {
        if(other !is University) return false
        return (this.name == other.name) && this.userId == other.userId
    }

    override fun toString(): String {
        return this.name
    }
}

===

Semester.class

@Parcelize
@Entity(
    foreignKeys = [ForeignKey(
        entity = University::class,
        parentColumns = ["name"],
        childColumns = ["universityName"],
        onDelete = ForeignKey.CASCADE,
        onUpdate = ForeignKey.CASCADE
    )], indices = [Index(value = ["universityName"])]
)
data class Semester(
    val title: String,
    val universityName: String,
    @PrimaryKey(autoGenerate = true) val id: Long
) : Parcelable

===

Course.class

@Parcelize
@Entity(
    foreignKeys = [ForeignKey(
        entity = Semester::class,
        parentColumns = ["id"],
        childColumns = ["semesterId"],
        onDelete = ForeignKey.CASCADE,
        onUpdate = ForeignKey.CASCADE
    )], indices = [Index(value = ["semesterId"])]
)
data class Course(
    val title: String,
    val code: String,
    val semesterId: Long,
    @PrimaryKey(autoGenerate = true) val id: Long
) : Parcelable

When I try to execute the following code, I get a foreign key constraint error:

val user = userDao.getUser()
        userDao.insertUniversity(University(user.id, "Chuckles University"))
        val university = userDao.getUniversitiesByUser(user.id)[0]
        userDao.insertSemester(Semester("Fall 22", "Comsats Wah", 0))
        val semester = userDao.getSemestersByUniversity(university.name)[0]
        userDao.insertCourse(Course("Introduction to Programming", "CSC100", semester.id, 0))

Answer 1

I get a foreign key constraint error:

That would likely be due to the University Comsats Wah not existing as by defining the FK you are saying that the Semester's UniversityName column MUST be a value that currently exists in the name column of a row in the University table.

Fix

Insert the Comsats Wah university or correct the parent university to be one that exists.

I discovered that autogenerated primary key field can't be used as a parent column

That is not the case. You can use an autogenerated primary key as a parent. What you cannot do, is have a null as a value(s) in a primary key, according to Room (SQLite itself does allow a null and this bug is sometimes exploited to indicate a self-reference).

In fact you have used this as the User id is autogenerated and is the parent of a University.

Demonstration of the Issue

Consider the following that demonstrates what you appear to be trying BUT with the exception trapped and a retry undertaken after adding the Comstats Wah university:-

    val uniname1 = "Chuckles University"
    val uniname2 = "Comsats Wah"
    val userEmail = "[email protected]"
    val userName = "user"

    db = TheDatabase.getInstance(this)
    dao = db.getAllDao()
    /* Clear Semesters and Universities */
    dao.clearSemesters()
    dao.clearUniversities()

    var userId = dao.insert(User(email = userEmail, name = userName, defaultRecipient = "blah"))
    /* If user already exists (-1 returned) then get the user by email */
    if (userId < 1) {
        userId = dao.getUserIdByEmail(userEmail)
    }

    var uniRowid = dao.insert(University(userId,uniname1))

    try {
        val university = dao.getUniversitiesByUser(userId)[0]
        dao.insertSemester(Semester("Fall 2022",uniname1,0))
        dao.insertSemester(Semester("Spring 2022",uniname1,0))
        dao.insertSemester(Semester("Fall 22", uniname2, 0)) /* FK CONFLICT due to Comsats Wah uni */
    } catch (e: SQLiteException) {
        e.printStackTrace()
    }

    try {
        dao.insert(University(userId,uniname2))
        dao.insertSemester(Semester("Fall 22", uniname2, 0)) /* Now Uni Comsats Wah exists inserts OK */
    } catch (e: SQLiteException) {
        e.printStackTrace()
    }

When run then the log includes:-

2022-06-04 06:30:49.966 W/System.err: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
2022-06-04 06:30:49.966 W/System.err:     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
2022-06-04 06:30:49.966 W/System.err:     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:938)
2022-06-04 06:30:49.966 W/System.err:     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
2022-06-04 06:30:49.966 W/System.err:     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
2022-06-04 06:30:49.966 W/System.err:     at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.java:51)
2022-06-04 06:30:49.966 W/System.err:     at androidx.room.EntityInsertionAdapter.insertAndReturnId(EntityInsertionAdapter.kt:102)


2022-06-04 06:30:49.967 W/System.err:     at a.a.so72489240fk.AllDao_Impl.insertSemester(AllDao_Impl.java:187)
2022-06-04 06:30:49.967 W/System.err:     at a.a.so72489240fk.MainActivity.onCreate(MainActivity.kt:38)


2022-06-04 06:30:49.967 W/System.err:     at android.app.Activity.performCreate(Activity.java:7994)
2022-06-04 06:30:49.967 W/System.err:     at android.app.Activity.performCreate(Activity.java:7978)
2022-06-04 06:30:49.967 W/System.err:     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1309)
2022-06-04 06:30:49.967 W/System.err:     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3422)
2022-06-04 06:30:49.968 W/System.err:     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3601)
2022-06-04 06:30:49.968 W/System.err:     at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:85)
2022-06-04 06:30:49.968 W/System.err:     at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
2022-06-04 06:30:49.969 W/System.err:     at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
2022-06-04 06:30:49.969 W/System.err:     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2066)
2022-06-04 06:30:49.969 W/System.err:     at android.os.Handler.dispatchMessage(Handler.java:106)
2022-06-04 06:30:49.969 W/System.err:     at android.os.Looper.loop(Looper.java:223)
2022-06-04 06:30:49.969 W/System.err:     at android.app.ActivityThread.main(ActivityThread.java:7656)
2022-06-04 06:30:49.973 W/System.err:     at java.lang.reflect.Method.invoke(Native Method)
2022-06-04 06:30:49.973 W/System.err:     at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:592)
2022-06-04 06:30:49.973 W/System.err:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:947)

The log indicates that the error was at MainActivity.onCreate(MainActivity.kt:38) i.e. line 38 which is dao.insertSemester(Semester("Fall 22", uniname2, 0)) /* FK CONFLICT due to Comsats Wah uni */. This as anticipated. However a subsequent trapped exception DOES NOT happen for line 45 which is dao.insertSemester(Semester("Fall 22", uniname2, 0)) /* Now Uni Comsats Wah exists inserts OK */

• note that the lines that show where the exception occurred have had blank lines added before and after to make them easy to see.

Instead inspecting the database via App Inspection shows:-

and :-

i.e. the Fall 22 Semester for *Comsats Wah has been added and it must have been after the Comstats Wah uni was added.

Additional

Your Schema has a flaw in that (as an example) is that you would need a University for every user, even if it were the same University as a University has the user as a parent. This compounded/complicated as a University name has to be unique as the name is the primary key which implies uniqueness.

What would be the more correct schema would be to have Universities without a User as a parent but to instead allow a University to be related to many Users and perhaps a User could have many Universities.

Likewise, perhaps Semesters could be common to Universities and perhaps even courses could be common to Universities and Semesters.

As such you could have Users, Universities, Semesters and Courses as tables/entities that have no relationships BUT then have tables that map/reference/relation/associate the various components that make up a user's study program.

The relationships could then be defined/set using a table or tables that map/reference/relate/associate (all different words for the same thing). This is where the use of generated id's come into play, they are simply more efficient (they exist anyway).

• SQLite handles what it calls rowid's more efficiently/faster

With regard to efficiency using autogenerate = true is inefficient. In short it includes the AUTOINCREMENT keyword and thus uses a table called sqlite_sequence that stores the highest allocated id, this additional table is then accessed and updated whenever inserting a new row.

SQLite advises against this, it says

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed. see - https://sqlite.org/autoinc.html

SQLite will generate a rowid if in Room you instead use:-

val id: Long?=null

So perhaps consider the following demo, that over comes, the issues mentioned.

So the basic classes could be (those ending in X being the improved/more correct version of the original classes without the X):-

User

@Entity
data class User(
    @PrimaryKey/*(autoGenerate = true)*/ val id: Long?=null,
    val email: String,
    val name: String,
    val defaultRecipient: String
)

UniversityX

@Entity(
    indices = [Index("universityXName", unique = true)] /* University name must be Unique (if wanted to be)*/
)
data class UniversityX(
    @PrimaryKey
    val universityXId: Long?=null,
    val universityXName: String
)

SemesterX

@Entity(
    indices = [
        Index(value = ["semesterXTitle"], unique = true)
    ]
)
data class SemesterX(
    @PrimaryKey
    val semesterXId: Long?=null,
    val semesterXTitle: String,
    val semesterXStartDate: Long,
    val semesterXEndDate: Long
)

CourseX

@Entity(
    indices = [Index("courseXTitle", unique = true)]
)
data class CourseX(
    @PrimaryKey
    val courseXId: Long?=null,
    val courseXTitle: String
)

To cater for relationships then an additional table that maps a User with the UniversityX and with the SemesterX and with the CourseX

• This is what is termed as a mapping/associative/reference table along with other names. This caters for multiple many-many relationships, which will be demonstrated.

As can be seen above the core tables above have no Foreign Keys defined. They are all within this mapping table UserUniversityXSemesterXCourseXMapping :-

@Entity(
    primaryKeys = [
        "uusc_userIdMapping","uusc_universityXIdMapping","uusc_semesterXIdMapping","uusc_courseXIdMapping"
    ],
    foreignKeys = [
        ForeignKey(User::class,["id"],["uusc_userIdMapping"], ForeignKey.CASCADE,ForeignKey.CASCADE),
        ForeignKey(UniversityX::class,["universityXId"],["uusc_universityXIdMapping"], ForeignKey.CASCADE,ForeignKey.CASCADE),
        ForeignKey(SemesterX::class,["semesterXId"],["uusc_semesterXIdMapping"], ForeignKey.CASCADE,ForeignKey.CASCADE),
        ForeignKey(CourseX::class,["courseXId"],["uusc_courseXIdMapping"],ForeignKey.CASCADE,ForeignKey.CASCADE)
    ]
)
data class UserUniversityXSemesterXCourseXMapping(
    val uusc_userIdMapping: Long,
    @ColumnInfo(index = true)
    val uusc_universityXIdMapping: Long,
    @ColumnInfo(index = true)
    val uusc_semesterXIdMapping: Long,
    @ColumnInfo(index = true)
    val uusc_courseXIdMapping: Long
)

Obviously at some time you would probably want to retrieve a User, along with the Uni, along with the Semester along with the Course. So a POJO for this could be UserWithUniversityAndSemesterAndCourse :-

data class UserWithUniversityAndSemesterAndCourse (
    @Embedded
    val user: User,
    @Embedded
    val universityX: UniversityX,
    @Embedded
    val semesterX: SemesterX,
    @Embedded
    val courseX: CourseX
    )

To access (insert/retrieve data) the above then AllDaoX is :-

@Dao
interface AllDaoX {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(user: User): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(universityX: UniversityX): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(semesterX: SemesterX): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(courseX: CourseX): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(semesterXCourseXMapping: SemesterXCourseXMapping): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(userUniversityXSemesterXCourseXMapping: UserUniversityXSemesterXCourseXMapping): Long

    @RewriteQueriesToDropUnusedColumns /* easier than doing SELECT user.*, universityX.*, semesterX.*, courseX.* .... instead of SELECT * .... */
    @Query("SELECT * FROM user " +
            "JOIN userUniversityXSemesterXCourseXMapping ON user.id = uusc_userIdMapping " +
            "JOIN universityX ON userUniversityXSemesterXCourseXMapping.uusc_universityXIdMapping = universityX.universityXId " +
            "JOIN semesterX ON userUniversityXSemesterXCourseXMapping.uusc_semesterXIdMapping = semesterX.semesterXId " +
            "JOIN courseX ON UserUniversityXSemesterXCourseXMapping.uusc_courseXIdMapping = courseX.courseXId")
    fun getUserWithAllUUSCs(): List<UserWithUniversityAndSemesterAndCourse>

    @Query("DELETE FROM UserUniversityXSemesterXCourseXMapping")
    fun clearUUSCMappingTable()
    @Query("DELETE FROM SemesterXCourseXMapping")
    fun clearSCMappIngTable()
    @Query("DELETE FROM courseX")
    fun clearCourseXTable()
    @Query("DELETE FROM semesterX")
    fun clearSemesterXTable()
    @Query("DELETE FROM universityX")
    fun clearUniversityXTable()

    @Transaction
    @Query("")
    fun clearAllXTables() {
        clearUUSCMappingTable()
        clearSCMappIngTable()
        clearCourseXTable()
        clearSemesterXTable()
        clearUniversityXTable()
    }

}

Most functions should be self-explanatory. However the SQL for the getUserWithAllUUSCs may require you to understand about JOINS. You may wish to refer to https://www.sqlite.org/lang_select.html

The clearAllXTables is an example of a function with a body as a Dao function (not allowable for a Java interface, so for java an abstract class would have to be used).

The empty @Query is to facilitate the use of the @Transaction, which should do everything within the function is a single transaction thus only writing to disk once instead of once for each function called.

Demonstrating the use of the above, for 2 Users participating in 2 courses each, the first at a single university, the second user at both universities and the same course as the first user (to demonstrate the common/many-many usage).

So consider:-

    val daoX = db.getAllDaoX()
    daoX.clearAllXTables()

    val user2 = daoX.insert(User(null,"another#mail.com","A N Other","blah"))

    val s4id = daoX.insert(SemesterX(null,"Autumn 22",Date("01/09/2022").time,Date("31/11/2022").time))
    val s3id = daoX.insert(SemesterX(null,"Winter 22",Date("01/09/2022").time,Date("31/11/2022").time))
    val u1id = daoX.insert(UniversityX(universityXName = "Uni1"))
    val s1id = daoX.insert(SemesterX(null,"Spring 22", Date("01/03/2022").time,Date("31/05/2022").time))
    val c1id = daoX.insert(CourseX(null,"M101 - Math"))
    val c2id = daoX.insert(CourseX(null,"M110 Applied Math"))
    val c3id = daoX.insert(CourseX(null,"E100 English Language"))
    val c4id = daoX.insert(CourseX(null,"C100 Chemistry"))
    val u2Id = daoX.insert(UniversityX(universityXName = "Uni2"))
    val s2id = daoX.insert(SemesterX(null,"Summer 22",Date("01/06/2022").time,Date("31/08/2022").time))


    daoX.insert(UserUniversityXSemesterXCourseXMapping(userId,u1id,s1id,c1id))
    daoX.insert(UserUniversityXSemesterXCourseXMapping(userId,u1id,s2id,c2id))
    daoX.insert(UserUniversityXSemesterXCourseXMapping(user2,u2Id,s4id,c4id))
    daoX.insert(UserUniversityXSemesterXCourseXMapping(user2,u1id,s1id,c1id))

    for(uwuasac in daoX.getUserWithAllUUSCs()) {
        Log.d(
            "DBINFO",
            "User is ${uwuasac.user.name} " +
                    "Email is ${uwuasac.user.email} " +
                    "Uni is ${uwuasac.universityX.universityXName} " +
                    "Sem is ${uwuasac.semesterX.semesterXTitle} " +
                    "Course is ${uwuasac.courseX.courseXTitle}"
        )
    }

• Note how the order of insertions of User's, Uni's Semesters, Courses is irrelevant. However, the respective User's Uni's etc MUST be inserted before the insertion of the mappings (UserUniversityXSemesterXCourseXMapping's) otherwsie FK constraint conflicts would result.

• The above does not cater for inadvertent duplicates (e.g. Same Uni).

When run, the first time only (the above demo is NOT intended to be run multiple times as duplicate handling has not been included to try to keep things simple) the log includes:-

D/DBINFO: User is user Email is [email protected] Uni is Uni1 Sem is Spring 22 Course is M101 - Math
D/DBINFO: User is user Email is [email protected] Uni is Uni1 Sem is Summer 22 Course is M110 Applied Math
D/DBINFO: User is A N Other Email is another#mail.com Uni is Uni2 Sem is Autumn 22 Course is C100 Chemistry
D/DBINFO: User is A N Other Email is another#mail.com Uni is Uni1 Sem is Spring 22 Course is M101 - Math

So user user has 2 courses both at the same Uni, across 2 semesters User A N Other has 2 courses, at each Uni, across the 2 other semesters noting that the course M101 is also used by the first user but in a different semester.

So any users can (if required) attend any course at any uni during any semester. Each Uni, Semester and Course only need to be stored once.

The Demo has not gone into the depths of Uni's having specific courses or specific semesters. But it demonstrates a more normalised schema.