How to make PrimaryKey to generate ids for multiple tables in one order

Question

I have 3 entities(Dishes, Places, Artifacts) which inherit from one abstract class called Memory. Every Memory can store up to 3 photos(string paths) so I set one to many relation Memory-Photo. Photo row contains memory Id as a foreign key so they can be linked together.

The problem is that Android Room creates separate tables for every type of memory so the @AutoGenerate = true annotation generates ids in 3 separate orders for every type of memory.

It causes conflicts because in database there might be 3 memories of different types with the same id so new set of photos would be linked to more than one memory.

I came up with an idea to make primary key out of creation timestamp but it is not the best idea. Maybe there is some way to synchronise key generation mechanism or change the plan of my database.

abstract class Memory(@ColumnInfo(name = "favorite") var favorite: Boolean,
    @ColumnInfo(name = "title") var title: String,
    @ColumnInfo(name = "date") var date: Date,
    @ColumnInfo(name = "description") var description: String,
    @Embedded var mapMarker: MapMarker) : Serializable {
    @ColumnInfo(name="id")
    @PrimaryKey(autoGenerate = true)
    var id: Long = 0 }

@Entity(tableName = "artifact_memories")
class ArtifactMemory(favorite: Boolean,
                 title: String,
                 date: Date,
                 description: String,
                 mapMarker: MapMarker) : Memory(favorite, title,
                                                date, description, mapMarker){}


@Entity(tableName = "dish_memories")
class DishMemory(@ColumnInfo(name = "country_of_origin") var originCountry: String,
    @ColumnInfo(name = "type") var dishType: String,
    favorite: Boolean,
    title: String,
    date: Date,
    description: String,
    mapMarker: MapMarker) : Memory(favorite, title,
                                   date, description, mapMarker) {}

@Entity(tableName = "photos")
data class Photo(@ColumnInfo(name="photo_path") var photoPath: String,
    @ColumnInfo(name="memory_id") var memoryId: Long = 0,
    @ColumnInfo(name="is_main") var main: Boolean = false) : Serializable {
    @PrimaryKey(autoGenerate = true) var id: Long = 0 }

How to deal with such relationships? The goal is to make photos be saved within only one memory and/or remove conflicts with id duplicates.

You may have to restructure your schema so that `Memory` itself is an entity, and each _sub-entity_ such as `Dish` or `Artifact` contains a `memoryId` that has a foreign key constraint on the auto-generated `Memory` id. In this way, each _sub-entity_ contains only the fields unique to it, while `Memory` contains common fields among all memories. To make it work with auto-generated ids, you would have to insert a `Memory`, retrieve its auto-generated id and use it to construct a _sub-entity_ such as `Dish`, which you then insert in the database. — , Aug 25 '19 at 04:36
The second-to-last paragraph in [this answer](https://stackoverflow.com/a/12041890/11566289) talks about the concept I mentioned above. — , Aug 25 '19 at 04:36
Another option, if you follow the concept I suggested, is to generate a random UUID for each memory, rather than having `Room` auto-generate it. This way, you can assign each _sub-entity_ its `memoryId` without having to first insert the `Memory`. — , Aug 25 '19 at 04:43
Thank you for suggestion. I am going to put both parent and children in separate tables as mentioned in the linked issue. — gypsy0ne, Aug 25 '19 at 07:27

score 0 · Answer 1 · answered Aug 27 '19 at 15:12

you can use inheritSuperIndices property to make sure that every child class inherits super class's primary key

@Entity(tableName = "artifact_memories",inheritSuperIndices = true)
class ArtifactMemory(favorite: Boolean,
             title: String,
             date: Date,
             description: String,
             mapMarker: MapMarker) : Memory(favorite, title,
                                            date, description, mapMarker){}


@Entity(tableName = "dish_memories",inheritSuperIndices = true)
class DishMemory(@ColumnInfo(name = "country_of_origin") var originCountry: 
String,
@ColumnInfo(name = "type") var dishType: String,
favorite: Boolean,
title: String,
date: Date,
description: String,
mapMarker: MapMarker) : Memory(favorite, title,
                               date, description, mapMarker) {}

@Entity(tableName = "photos",inheritSuperIndices = true)
data class Photo(@ColumnInfo(name="photo_path") var photoPath: String,
@ColumnInfo(name="memory_id") var memoryId: Long = 0,
@ColumnInfo(name="is_main") var main: Boolean = false) : Serializable {
@PrimaryKey(autoGenerate = true) var id: Long = 0 }

It does not solve the problem. Separate tables are created for each memory child, and their ids are duplicated. — gypsy0ne, Aug 27 '19 at 17:03

How to make PrimaryKey to generate ids for multiple tables in one order

1 Answers1