How can I link multiple tables using foreign key in Room database (kotlin)

Question

/*table-1*/
@Entity(tableName = "Doc_Type_Table")
data class DocTypeModel(
    @ColumnInfo(name = "title")
    var title : String,
    @ColumnInfo(name = "date")
    var date : String,
    @ColumnInfo(name = "color_code")
    var colorCode : String,
){
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    var id: Int = 0
}


/*table-2*/
@Entity(tableName = "Field_Table",
    foreignKeys = [ForeignKey(
        entity = DocTypeModel::class,
        childColumns = ["doc_type_id"],
        parentColumns = ["id"],
        onDelete = ForeignKey.CASCADE
    )])
data class FieldModel(
    @ColumnInfo(name = "doc_type_id")
    var docTypeId: Int,
    @ColumnInfo(name = "name")
    var name : String,
    @ColumnInfo(name = "field_type")
    var fieldType : Int,
    @ColumnInfo(name = "value")
    var value : String,
)
{
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    var id: Int = 0
}



/*table-3*/
@Entity(tableName = "Field_Type_Table",
    foreignKeys = [ForeignKey(
        entity = FieldModel::class,
        childColumns = ["field_type"],
        parentColumns = ["field_type"],
        onDelete = ForeignKey.CASCADE
    )])
data class FieldTypeModel(
    @ColumnInfo(name = "field_type")
    var fieldTypeId: Int,
    @ColumnInfo(name = "type")
    var fieldType : String
)
{
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    var id: Int = 0
}

---

error: com.demo.com.model.FieldTypeModel has a foreign key (field_type) that references com.demo.com.model.FieldModel (field_type) but com.demo.com.model.FieldModel does not have a unique index on those columns nor the columns are its primary key. SQLite requires having a unique constraint on referenced parent columns so you must add a unique index to com.demo.com.model.FieldModel that has (field_type) column(s).
public final class FieldModel {

Answer 1

What the error is saying, a child MUST reference one and only one parent, and therefore the reference to the parent MUST have the UNIQUE constraint (a rule that says the value in the column(s) cannot be the same in multiple rows).

What the error is saying, in respect to your schema, is that the field_type column in the Field_Table table (the parent) does not have the UNIQUE constraint and can therefore be the same value in more than 1 row.

It is not clear what you trying to achieve, but it would appear that one of the two following solutions would be what you want:-

Solution 1

If a Field_Type_Table row is a child of a Field_Table row and thus that many Field_Types_Table rows can be a child of a Field_Table row then the field_type column in the Field_Table table is probably superfluous and you could have/use:-

/*table-3*/
@Entity(tableName = "Field_Type_Table",
    foreignKeys = [ForeignKey(
        entity = FieldModel::class,
        childColumns = ["field_type"],
        parentColumns = ["id"], //<<<<<<<<<< CHANGED id is Primary Key so is implicitly UNQIUE
        onDelete = ForeignKey.CASCADE
    )])

• If this is the solution than it is suggested to also use @ColumnInfo(name = "doc_type_id", index = true) instead of @ColumnInfo(name = "doc_type_id") and @ColumnInfo(name = "field_type", index = true) instead of @ColumnInfo(name = "field_type") (in the Field_Type_Table).
  • indexes on the child column can improve efficiency.

Solution 2

If the field_type column in the Field_Table is meant to reference a row in the Field_Type_Table and that many Field_Table rows can reference (be a child of) the same Field_Type_Table then the field_type column in the Field_Type_Table is probably superfluous and you could then use:-

/*table-2*/
@Entity(tableName = "Field_Table",
    foreignKeys = [ForeignKey(
        entity = DocTypeModel::class,
        childColumns = ["doc_type_id"],
        parentColumns = ["id"],
        onDelete = ForeignKey.CASCADE
    ),
        ForeignKey(
            entity = FieldTypeModel::class,
            childColumns = ["field_type"],
            parentColumns = ["id"],
            onDelete = ForeignKey.CASCADE
    )]
    )

Along with

/*table-3*/
@Entity(tableName = "Field_Type_Table")

• If this is the solution than it is suggested to also use @ColumnInfo(name = "doc_type_id", index = true) instead of @ColumnInfo(name = "doc_type_id") and @ColumnInfo(name = "field_type", index = true) instead of @ColumnInfo(name = "field_type") (in the Field_Table).
  • indexes on the child column can improve efficiency.