Room compile problem - column references a foreign key but it is not part of an index

user_12245833 0
Viewed 4801

I'm new to android app development and am creating an app that has Trips which stores Locations.

I'm getting a compile error :"trip_Id column references a foreign key but it is not part of an index. This may trigger full table scans whenever parent table is modified so you are highly advised to create an index that covers this column."

I have 2 tables: Trip & Location.

I've tried indexing the tripId and the locationId in their respective classes but it doesn't solve the issue.

A Trip has its id (PK), title, description and priority.

A Location has its locationId (PK), tripId(FK), locationName and LatLng of the place.

@Entity(tableName = "location_table",
        foreignKeys = @ForeignKey(entity = Trip.class, parentColumns = "location_Id", childColumns = "trip_Id"),
        indices = {@Index(value = {"locationId"}, unique = true)})
public class Location {

    @PrimaryKey(autoGenerate = true)
    private int locationId;

    @ColumnInfo (name = "trip_Id")
    private int tripId;

    private String locationName;

    @TypeConverters(LatLngConverter.class)
    private LatLng latLng;


@Entity(tableName = "trip_table", indices = {@Index(value = {"id"}, unique = true)})

public class Trip {

    @PrimaryKey(autoGenerate = true) // with each new row SQLite will automatically increment this ID so it will be unique
    @ColumnInfo (name = "location_Id")
    private int id;

    private String title;

    private String description;

    private int priority;

I can't seem to find out what's wrong

android-room android-sqlite android java
2 Answers

That message is a warning, there would be other errors (see 2nd suggested line). e.g.

enter image description here

Using indices = {@Index(value = {"locationId"}, unique = true),@Index(value = {"trip_Id"})}) should overcome that warning.

HOWEVER, there is no need to have an (additional) index on locationId as it is already indexed being the primary key (this would be a waste and also inefficient). So it is suggested that you use :-

indices = {@Index(value = {"trip_Id"})})

I believe that your overall issue is that you are referring to the object's variable name for the column, when if you have @ColumnInfo(name ="????") then you should be referring to the given name

  • i.e. ???? is the column name in the underlying table.

You should also be using location_Id instead of id in the Trip:-

@Entity(tableName = "trip_table", indices = {@Index(value = {"location_Id"}, unique = true)})
user_47445140

I had this problem when I had quite a few foreign keys. I found out (after a lot of trial and error) that I set up my indices wrong... whoops. :P

Wrong:

indices = [
    Index("active_color_style_id",
        "ambient_color_style_id",
        "hour_hand_dimensions_id",
        "minute_hand_dimensions_id",
        "second_hand_dimensions_id")
]

Right:

indices = [
    Index("active_color_style_id"),
    Index("ambient_color_style_id"),
    Index("hour_hand_dimensions_id"),
    Index("minute_hand_dimensions_id"),
    Index("second_hand_dimensions_id")
]

Full example:

@Entity(
    tableName = "analog_watch_face_table",
    foreignKeys = [
        ForeignKey(
            entity = WatchFaceColorStyleEntity::class,
            parentColumns = ["id"],
            childColumns = ["active_color_style_id"],
            onDelete = NO_ACTION
        ),
        ForeignKey(
            entity = WatchFaceColorStyleEntity::class,
            parentColumns = ["id"],
            childColumns = ["ambient_color_style_id"],
            onDelete = NO_ACTION
        ),
        ForeignKey(
            entity = WatchFaceArmDimensionsEntity::class,
            parentColumns = ["id"],
            childColumns = ["hour_hand_dimensions_id"],
            onDelete = NO_ACTION
        ),
        ForeignKey(
            entity = WatchFaceArmDimensionsEntity::class,
            parentColumns = ["id"],
            childColumns = ["minute_hand_dimensions_id"],
            onDelete = NO_ACTION
        ),
        ForeignKey(
            entity = WatchFaceArmDimensionsEntity::class,
            parentColumns = ["id"],
            childColumns = ["second_hand_dimensions_id"],
            onDelete = NO_ACTION
        )
    ],
    indices = [
        Index("active_color_style_id"),
        Index("ambient_color_style_id"),
        Index("hour_hand_dimensions_id"),
        Index("minute_hand_dimensions_id"),
        Index("second_hand_dimensions_id")
    ]
)
user_16311970
Related