sqlite foreign-keys android-room entity many-to-one

Embed a many to one Room DAO object with multiple occurence of single entry in two different spots in the data hiearachy

I have the following datastructure Jobs includes a list of Workers and a list of Posters Posters have a single worker.

I am able to get the list of workers and posters embedded into the Job object; but I am unable to get the single worker into the poster object.

I have tried using relations and entities but have run into a problem that entities cannot include relations, and in order to be able to include something as a relation it has to be an entity. That means that when I try to add the Worker relation to the Poster object using a PosterWithWorker class the JobsWithWorkersAndPosters rejects it because it isn't an Entity and I can't make it an entity.

I tried using Foreign keys to map the worker table into the poster table. Here is a snapshot of those classes

@Entity
    (tableName = "poster")
data class Poster (
    @PrimaryKey val poster_id: Int,
    val job_id: Int,
    val qr_code: String,
    var status: Int,
    @ColumnInfo(name = "worker_id", index = true)
    val worker_id: Int,
    val longitude: Double,
    val latitude: Double)

@Entity(tableName = "worker",
        foreignKeys = [
        androidx.room.ForeignKey(
            entity = Poster::class,
            parentColumns = ["poster_id"],
            childColumns = ["worker_id"],
            onDelete = CASCADE,
            onUpdate = CASCADE
        )])
data class Worker(
    @PrimaryKey
    @ColumnInfo(name = "worker_id")
    val worker_id: Int,
    val workerName: String,

)

data class PosterWithWorker(
        @Embedded val poster: Poster,
        @Relation(
            parentColumn = "poster_id",
            entityColumn = "worker_id"
        )
        val worker:  Worker
)

data class JobWithPostersAndWorkers(
    @Embedded val job: Job,
    @Relation(
        parentColumn = "job_id",
        entityColumn = "worker_id",
        associateBy = Junction(JobWorkerCrossRef::class)
    )
    val workers: List<Worker>,

    @Relation(entity=Poster::class, parentColumn = "job_id", entityColumn = "job_id")
    val poster: List<PosterWithWorker>
)

data class PosterWithWorker(
        @Embedded val poster: Poster,
        @Relation(
            parentColumn = "poster_id",
            entityColumn = "worker_id"
        )
        val worker:  Worker
)

I was following this example Android room - 3 one-to-many relationship and tried to follow the same pattern of nesting.

I tried different variations and got different errors.

My first question is if this is possible to accomplish without using Foreign Keys and then what am I doing wrong.

Solution

My first question is if this is possible to accomplish without using Foreign Keys and then what am I doing wrong.

Yes, the referenced answer includes:-

foreignKeys are optional but enforce and help maintain referential integrity. Within foreign keys the onDelete and onUpdate are optional

A Foreign Key, in SQLite, which is what Room is a wrapper around, is a constraint (a rule) where the value in the column(s) to which the FK constraint is applied to (the CHILD) MUST be a value that exists in the column(s) that is(are) referenced (the PARENT). i.e known as Referential Integrity. Another way of thinking of this is that Foreign Key constraints prohibit Orphans.

In your scenario (and according to the comment I'd like to add a worker field to the poster class. There will only be one worker per poster.)

By using:-

@Entity(tableName = "worker",
        foreignKeys = [
        androidx.room.ForeignKey(
            entity = Poster::class,
            parentColumns = ["poster_id"],
            childColumns = ["worker_id"],
            onDelete = CASCADE,
            onUpdate = CASCADE
        )])
data class Worker(
    @PrimaryKey
    @ColumnInfo(name = "worker_id")
    val worker_id: Int,
    val workerName: String,

)

You are affectively saying that the worker_id column MUST be an existing value in the poster_id column of the Poster table. Thus limiting the number of workers to the number of Posters.

I believe what you want is :-

@Entity(
    tableName = "poster",
    foreignKeys = [
        ForeignKey(
            entity = Worker::class,
            parentColumns = ["worker_id"],
            childColumns = ["worker_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class Poster (
    @PrimaryKey val poster_id: Int,
    val job_id: Int,
    val qr_code: String,
    var status: Int,
    @ColumnInfo(name = "worker_id", index = true)
    val worker_id: Int,
    val longitude: Double,
    val latitude: Double)

@Entity(
    tableName = "worker",
    /*
    foreignKeys = [
        androidx.room.ForeignKey(
            entity = Poster::class,
            parentColumns = ["poster_id"],
            childColumns = ["worker_id"],
            onDelete = CASCADE,
            onUpdate = CASCADE
        )]*/
)
data class Worker(
    @PrimaryKey
    @ColumnInfo(name = "worker_id")
    val worker_id: Int,
    val workerName: String,
    )

data class PosterWithWorker(
    @Embedded val poster: Poster,
    @Relation(
        parentColumn = "worker_id",
        entityColumn = "worker_id"
    )
    val worker:  Worker
)

Note the commented out FKey in the Worker class.

i.e. The Poster includes a reference to the single Worker (who must exist). That is the Poster is (Posters are) the child of the Worker. A Worker may have many children (Posters).

Additional

Jobs includes a list of Workers and a list of Posters, Posters have a single worker.

Which is then contradicted by the comment

Actually there is another confusion here ideally a poster doesn't need a worker.

You also comment

I tried your solution and I'm getting a FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY) error.

This because the are no rows that are null in the parent table thus RULE broken if you try to have a reference to no Poster.

When defining Foreign Keys then the column where the FK is defined will be the child and the referenced column will be the parent.

Saying that here is a solution that allows a Job to have many (0-millions of) workers and that the same worker can part of many Jobs. a many-many relationship. It also caters for the Awkward Poster-Worker but also caters for Referential Integrity (Foreign Keys).

So you can start of with 3 core tables Job, Worker and Poster. The first two are independent of other tables. e.g.

@Entity
data class Job(
    @PrimaryKey
    val job_id: Long?=null,
    val jobName: String
)
@Entity
data class Worker(
    @PrimaryKey
    @ColumnInfo(name = "worker_id")
    val worker_id: Long?=null,
    val workerName: String,
)

As a Poster MUST have a Job as it's parent and a Job could have Many Posters (i.e. a list of posters). This is a 1 (Job) to many (Poster). A Foreign Key may be used to enforce Referential Integrity. i.e. a Poster cannot exist without a parent. So:-

@Entity(
    foreignKeys = [
        /* POSTER MUST be a child of a Job (no Job no poster)*/
        ForeignKey(
            entity = Job::class,
            parentColumns = ["job_id"],
            childColumns = ["job_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class Poster(
    @PrimaryKey
    val poster_id: Long?=null,
    @ColumnInfo(index = true)
    val job_id_map: Long, /* CHILD OF Job */
    val qr_code: String,
    var status: Int,
    val longitude: Double,
    val latitude: Double
)

Next the list of Workers per Job and the assumption that a Worker could be a Worker on many jobs and thus a many0many relationship and thus an intermediate/cross reference/mapping/associative .... table.

So:-

@Entity(
    primaryKeys = ["job_id_map","worker_id_map"], /* composite primary key */
    foreignKeys = [
        ForeignKey(
            entity = Job::class,
            parentColumns = ["job_id"],
            childColumns = ["job_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Worker::class,
            parentColumns = ["worker_id"],
            childColumns = ["worker_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class JobWorkerMap(
    val job_id_map: Long,
    @ColumnInfo(index = true)
    val worker_id_map: Long,
)

this already covered previously.

At this stage the Poster's Worker has been omitted. So the no Worker situation is possible. However, to allow for a Worker a mapping table can be used (0-many) BUT the need is to limit to a single worker if one. This can be accomplished by having the primary key on just the Poster column rather than on a composite of both columns. So:-

@Entity
data class PosterWorkerRestrictedMap(
    @PrimaryKey /*Primary Key restricts to 1 poster */
    val poster_id_map: Long,
    @ColumnInfo(index = true)
    val worker_id_map: Long
)

To support the inclusion of this 0 or 1 Worker for a Poster then:-

data class PosterWithWorkerOrNot(
    @Embedded
    val poster: Poster,
    @Relation(
        entity = Worker::class,
        parentColumn = "poster_id",
        entityColumn = "worker_id",
        associateBy = Junction(
            value = PosterWorkerRestrictedMap::class,
            parentColumn = "poster_id_map",
            entityColumn = "worker_id_map"
        )
    )
    val worker: List<Worker> /* List should be empty or have 1 element */
)

Finally to cater for the Job with it's Workers and with it's PostersWithWorkerOrNot then:-

data class JobWithWorkersAndPosterWithPosterWorker(
    @Embedded
    val job:Job,
    @Relation(
        entity = Worker::class,
        parentColumn = "job_id",
        entityColumn = "worker_id",
        associateBy = Junction(
            value = JobWorkerMap::class,
            parentColumn = "job_id_map",
            entityColumn = "worker_id_map"
        )

    )
    val workers: List<Worker>,
    @Relation(
        entity = Poster::class,
        parentColumn = "job_id",
        entityColumn = "job_id_map"
    )
    val posters: List<PosterWithWorkerOrNot>
)

To demonstrate some DAO functions:-

@Dao
interface TheDAOs {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(worker: Worker): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(poster: Poster): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(job: Job): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(jobWorkerMap: JobWorkerMap): Long
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insert(posterWorkerRestrictedMap: PosterWorkerRestrictedMap): Long

    @Transaction
    @Query("SELECT * FROM job")
    fun getAllJobsWithWorkersAndPostersWithPosterWorker(): List<JobWithWorkersAndPosterWithPosterWorker>
}

i.e. the ability to insert into all of the tables and also the query to extract Jobs with the list of Workers with the list of Posters each of which may or may not have a Poster Worker.

To actually demonstrate the following:-

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

    val w1id = dao.insert(Worker(null,"W1"))
    val w2id = dao.insert(Worker(null,"W2"))
    val w3id = dao.insert(Worker(null,"W3"))
    val w4id = dao.insert(Worker(null,"W4"))
    val w5id = dao.insert(Worker(null,"W5"))

    val j1id = dao.insert(Job(null,"J1"))
    val j2id = dao.insert(Job(null,"J2"))
    val j3id = dao.insert(Job(null,"J3"))

    val p1id = dao.insert(Poster(null,j1id,"QRXXX",1,10.132,20.78))
    val p2id = dao.insert(Poster(null,j2id,"QRYYY",2,1.333,12.765))
    val p3id = dao.insert(Poster(null,j2id,"QRZZZ",3,2.456,13.675))
    val p4id = dao.insert(Poster(null,j1id,"QRAAA",2,3.213,14.902))

    dao.insert(JobWorkerMap(j1id,w2id))
    dao.insert(JobWorkerMap(j1id,w3id))
    dao.insert(JobWorkerMap(j1id,w1id))
    dao.insert(JobWorkerMap(j2id,w4id))
    dao.insert(JobWorkerMap(j2id,w5id))
    dao.insert(JobWorkerMap(j2id,w3id))
    dao.insert(JobWorkerMap(j2id,w1id))
    logAll("_R1")

    dao.insert(PosterWorkerRestrictedMap(p1id,w2id))
    dao.insert(PosterWorkerRestrictedMap(p2id,w4id))
    dao.insert(PosterWorkerRestrictedMap(p3id,w5id))
    logAll("_R2")


}

fun logAll(suffix: String) {
    for (jwwapwpw in dao.getAllJobsWithWorkersAndPostersWithPosterWorker()) {
        val sbwl = StringBuilder()
        val sbpl = StringBuilder()
        for (w in jwwapwpw.workers) {
            sbwl.append("\n\tWorker is ${w.workerName} ID is ${w.worker_id}")
        }
        for (p in jwwapwpw.posters) {
            sbpl.append("\n\tPoster is ${p.poster.qr_code}")
            if (p.worker.size > 0) {
                sbpl.append(" PosterWorker is ${p.worker[0].workerName} ID is ${p.worker[0].worker_id}")
            } else {
                sbpl.append(" NO POSTER WORKER")
            }
        }
        Log.d("DBINFO${suffix}","JOB IS ${jwwapwpw.job.jobName} it has ${jwwapwpw.workers.size} Workers and ${jwwapwpw.posters.size}. posters. They are${sbwl}${sbpl} ")
    }
}

Result the output to the log:-

2023-03-07 16:50:29.911 D/DBINFO_R1: JOB IS J1 it has 3 Workers and 2. posters. They are
        Worker is W1 ID is 1
        Worker is W2 ID is 2
        Worker is W3 ID is 3
        Poster is QRXXX NO POSTER WORKER
        Poster is QRAAA NO POSTER WORKER 
2023-03-07 16:50:29.911 D/DBINFO_R1: JOB IS J2 it has 4 Workers and 2. posters. They are
        Worker is W1 ID is 1
        Worker is W3 ID is 3
        Worker is W4 ID is 4
        Worker is W5 ID is 5
        Poster is QRYYY NO POSTER WORKER
        Poster is QRZZZ NO POSTER WORKER 
2023-03-07 16:50:29.911 D/DBINFO_R1: JOB IS J3 it has 0 Workers and 0. posters. They are 



2023-03-07 16:50:29.932 D/DBINFO_R2: JOB IS J1 it has 3 Workers and 2. posters. They are
        Worker is W1 ID is 1
        Worker is W2 ID is 2
        Worker is W3 ID is 3
        Poster is QRXXX PosterWorker is W2 ID is 2
        Poster is QRAAA NO POSTER WORKER 
2023-03-07 16:50:29.932 D/DBINFO_R2: JOB IS J2 it has 4 Workers and 2. posters. They are
        Worker is W1 ID is 1
        Worker is W3 ID is 3
        Worker is W4 ID is 4
        Worker is W5 ID is 5
        Poster is QRYYY PosterWorker is W4 ID is 4
        Poster is QRZZZ PosterWorker is W5 ID is 5 
2023-03-07 16:50:29.932 D/DBINFO_R2: JOB IS J3 it has 0 Workers and 0. posters. They are

Note that R1 is before any Workers have been assigned to any of the Posters. R2 is after some Workers have been assigned. Hence the output has been split to make it easier to see.
As can be seen Job J1 has 2 Posters one with and the other without a worker. Potential issues with nulls have been avoided as has the FK error for when there is no Worker for a Poster.