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