Primary and foreign key implementation Java and Android Room

Question

This is my first time asking a question here in StackOverflow so forgive me if I am not asking the question right or a certain way.

I have two child classes (PerformanceAssessment and ObjectiveAssessment) from parent class Assessment. I was able to successfully do downcasting for the polymorphism part requirement of my school project, and I was able to save to their appropriate databases both instances of parent and child after downcasting (I have an assessment_table, performance_assessments, and objective_assessments table). However, I am now having Android Room database issues. I realized that I need to implement foreign keys to do the CRUD operations properly. How do I tweak my parent and child classes code and add proper annotations for implementing foreign and primary keys correctly? I need to have an autogenerated primary key for each class: parent (Assessment) and both children (PerformanceAssessment and ObjectiveAssessment). But I also need to utilize the children's primary key as a foreign key for the parent's databases so that when I delete an instance of Performance/Objective assessments, I can also delete the Assessment instance when I am downcasting. I found very little helpful information regarding this. Thanks in advance.

I am getting these errors right now:

Build Errors

Parent Class: Assessment.java

@Entity(tableName = "assessment_table")
public class Assessment {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "assessment_id")
    private final int assessment_id;

    private String assessmentName;
    private String assessmentStart;
    private String assessmentEnd;
    private int courseID;

    public Assessment(int assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, int courseID) {
        this.assessment_id = assessment_id;
        this.assessmentName = assessmentName;
        this.assessmentStart = assessmentStart;
        this.assessmentEnd = assessmentEnd;
        this.courseID = courseID;
    }

Child class: PerformanceAssessment.java

@Entity(tableName = "performance_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "performance_id",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class PerformanceAssessment extends Assessment{

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "performance_id")
    private int performanceID;
    private String type;

    public PerformanceAssessment(int assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, int courseID, int performanceID, String type) {
        super(assessment_id, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.performanceID = performanceID;
        this.type = type;
    }

Child class: ObjectiveAssessment.java

@Entity(tableName = "objective_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "objective_id",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class ObjectiveAssessment extends Assessment{

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "objective_id")
    private int objective_ID;
    private String type;

    public ObjectiveAssessment(int assessmentID, String assessmentName, String assessmentStart, String assessmentEnd, int courseID, int objective_ID, String type) {
        super(assessmentID, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.objective_ID = objective_ID;
        this.type = type;
    }

Here is the part where I am adding a new assessment in the app:

public void saveAssessment(View view) {

        assessmentTitle = editName.getText().toString();
        assessmentStart = editStart.getText().toString();
        assessmentEnd = editEnd.getText().toString();

        //Check if fields are empty:
        if (assessmentTitle.isEmpty() || assessmentStart.isEmpty() || assessmentEnd.isEmpty()) {

            Toast.makeText(AddAssessmentScreen.this, "Fill out required fields.", Toast.LENGTH_LONG).show();
            return;

        }
        else {

            //Check assessment type selected (used Downcasting for polymorphism):

            if (assessment_type == true) {
                Assessment performanceAssessment = new PerformanceAssessment(0, assessmentTitle, assessmentStart, assessmentEnd, currentCourseID, 0, selectedString);
                PerformanceAssessment castedPerformance = (PerformanceAssessment) performanceAssessment;
                //Insert assessment to database performance_assessment table (Performance child type):
                repository.insert(castedPerformance);
                Repository addToAssessment = new Repository(getApplication());
                //Insert assessment to database assessment_table (Assessment parent type):
                addToAssessment.insert(performanceAssessment);

            }
            else {
                Assessment objectiveAssessment = new ObjectiveAssessment(0,assessmentTitle, assessmentStart, assessmentEnd, currentCourseID, 0, selectedString);
                ObjectiveAssessment castedObjective = (ObjectiveAssessment) objectiveAssessment;
                //Insert assessment to database objective_assessment table (Objective child type):
                repository.insert(castedObjective);
                Repository addToAssessment = new Repository(getApplication());
                //Insert assessment to database assessment_table (Assessment parent type):
                addToAssessment.insert(objectiveAssessment);
            }

            Toast.makeText(AddAssessmentScreen.this, "New assessment added. Refresh previous screen.", Toast.LENGTH_LONG).show();

        }

Answer 1

How do I tweak my parent and child classes code and add proper annotations for implementing foreign and primary keys correctly?

Let the assessment_id always be the primary key i.e. don't code it in sub classes and then have a field for the reference/map/association with the parent in the subclasses.

So PerformanceAssessment could be :-

@Entity(tableName = "performance_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "assessment_reference",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class PerformanceAssessment extends Assessment {

    private int assessment_reference; //<<<<<<<<<
    private String type;

    public PerformanceAssessment(int assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, int courseID, int assessment_reference, String type) {
        super(assessment_id, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }
    ....

and all compiles and the underlying tables will be build using :-

    _db.execSQL("CREATE TABLE IF NOT EXISTS `assessment_table` (`assessment_id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `assessmentName` TEXT, `assessmentStart` TEXT, `assessmentEnd` TEXT, `courseID` INTEGER NOT NULL)");
    _db.execSQL("CREATE TABLE IF NOT EXISTS `performance_assessment` (`assessment_reference` INTEGER NOT NULL, `type` TEXT, `assessment_id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `assessmentName` TEXT, `assessmentStart` TEXT, `assessmentEnd` TEXT, `courseID` INTEGER NOT NULL, FOREIGN KEY(`assessment_reference`) REFERENCES `assessment_table`(`assessment_id`) ON UPDATE CASCADE ON DELETE CASCADE )");

• Obviously similar for the ObjectiveAssessment.

But I also need to utilize the children's primary key as a foreign key for the parent's databases so that when I delete an instance of Performance/Objective assessments, I can also delete the Assessment instance when I am downcasting.

What if, which is possible with the 1 (Assessment) - Many (PerformanceAssessments), an Assessment has multiple PerformanceAssesments? Deleting 1 PerformanceAsessment would delete the parent Assessment, which would then, due to the use of onDelete CASCADE cascade the deletions to all the other PerformanceAssessments and ObjectiveAssessments. Deletions are not propagated up to the parent (that's why the term CASCADE is used as it implies downward rather than anyway).

As an example you have an assessment with 4 PerformanceAssessments and lets say 3 ObjectiveAssessments one of the PerformanceAssesments was wrongly added. Should everything have to be deleted and re-entered to correct the 1 wrong PeformanceAssessment.

You could introduce a Trigger to automate this upwards propogation, to delete anything and delete all, if that is what you want.

Additional Re comments

I have a one to one relationship for the Assessment and Performance/Objective assessment. One assessment can only have either performance or objective type.

That may be what you wish BUT there is nothing stopping an Assessment having multiple Performance and or Objectives. That may or may not be an issue.

Also, instead of downcasting, I did upcasting instead on where I save the assessment in the app. I ran into Foreign Key Constraint Failed (code 787) and realized it was because I was downcasting. My only dilemma now is that whenever I am saving an assessment, it will only save on assessment_table and not on performance_assessment or objective_assessment tbl.

What you have to do is insert the Assessment (parent) and then use the parent's id to insert the related Peformance/Objective with the assessment_reference set the the value of the assessment.

The 787 is it saying that you cannot insert a child (Performance/Objective) without the assessment_reference being an assessment_id in the Assessment.

What you can do, which would very likely suit your situation is a) have an @Insert long insert(Assessment assessment); and an @Insert long (PerformanceAssessment performanceAssessment) (likewise for Objective)

and then use (assuming dao is an instance of the @Dao)

and use

dao.insert(new PerformanceAssessment(0,"the title", etc,dao.insert(Assessment(....),"the type");

So the Assessment is added as part of inserting the performance/Objective and with the id of the assessment being used as the assessment_reference value.

• NOTE the @Insert will only return a long, so you have to convert this to an int. I would suggest changing id's and references to be long's. The overhead, if any, will have a minimal impact. Storage in the database will not be affected and it will save the hassle of converting long to int.

Demonstration

The following is a demonstration based upon code that closely reflects your but with some subtle changes.

1. id's have been changed to be long rather than int.
2. autogenerate has been removed BUT the id's will be generated as before. 2. All that autogenerate does from an SQLite standpoint is include the AUTOINCREMENT key word. This is actually inefficient and NOT recommended. https://sqlite.org/autoinc.html

So the code is :-

Assessment

@Entity(tableName = "assessment_table")
public class Assessment {

    @PrimaryKey /* no need for autogenerate */
    @ColumnInfo(name = "assessment_id")
    /* As not autogenerate then use Long and default to null */
    /* same as autogenerate but without the overheads/inefficiencies */
    private Long assessment_id = null; //Long rather than long so can be null and have id generated.
    private String assessmentName;
    private String assessmentStart;
    private String assessmentEnd;
    private long courseID;

    public Assessment(long assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, long courseID) {
        this.assessment_id = assessment_id;
        this.assessmentName = assessmentName;
        this.assessmentStart = assessmentStart;
        this.assessmentEnd = assessmentEnd;
        this.courseID = courseID;
    }

    @Ignore
    /* Alternative constructor no need to provide id for inserting */
    public Assessment(String assessmentName, String assessmentStart, String assessmentEnd, long courseID) {
        this.assessmentName = assessmentName;
        this.assessmentStart = assessmentStart;
        this.assessmentEnd = assessmentEnd;
        this.courseID = courseID;
    }

    public Long getAssessment_id() {
        return assessment_id;
    }

    public void setAssessment_id(Long assessment_id) {
        this.assessment_id = assessment_id;
    }

    public String getAssessmentName() {
        return assessmentName;
    }

    public void setAssessmentName(String assessmentName) {
        this.assessmentName = assessmentName;
    }

    public String getAssessmentStart() {
        return assessmentStart;
    }

    public void setAssessmentStart(String assessmentStart) {
        this.assessmentStart = assessmentStart;
    }

    public String getAssessmentEnd() {
        return assessmentEnd;
    }

    public void setAssessmentEnd(String assessmentEnd) {
        this.assessmentEnd = assessmentEnd;
    }

    public long getCourseID() {
        return courseID;
    }

    public void setCourseID(long courseID) {
        this.courseID = courseID;
    }
}

PerformanceAssessment

@Entity(tableName = "performance_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "assessment_reference",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class PerformanceAssessment extends Assessment {

    private long assessment_reference;
    private String type;

    public PerformanceAssessment(long assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, long courseID, long assessment_reference, String type) {
        super(assessment_id, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }

    @Ignore
    public PerformanceAssessment(String assessmentName, String assessmentStart, String assessmentEnd, long courseID, long assessment_reference, String type) {
        super(assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }

    @Override
    public Long getAssessment_id() {
        return super.getAssessment_id();
    }

    @Override
    public void setAssessment_id(Long assessment_id) {
        super.setAssessment_id(assessment_id);
    }

    @Override
    public String getAssessmentName() {
        return super.getAssessmentName();
    }

    @Override
    public void setAssessmentName(String assessmentName) {
        super.setAssessmentName(assessmentName);
    }

    @Override
    public String getAssessmentStart() {
        return super.getAssessmentStart();
    }

    @Override
    public void setAssessmentStart(String assessmentStart) {
        super.setAssessmentStart(assessmentStart);
    }

    @Override
    public String getAssessmentEnd() {
        return super.getAssessmentEnd();
    }

    @Override
    public void setAssessmentEnd(String assessmentEnd) {
        super.setAssessmentEnd(assessmentEnd);
    }

    @Override
    public long getCourseID() {
        return super.getCourseID();
    }

    @Override
    public void setCourseID(long courseID) {
        super.setCourseID(courseID);
    }

    public long getAssessment_reference() {
        return assessment_reference;
    }

    public void setAssessment_reference(long assessment_reference) {
        this.assessment_reference = assessment_reference;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }
}

ObjectiveAssessment

@Entity(tableName = "objective_assessment", foreignKeys = {
        @ForeignKey(
                entity = Assessment.class,
                parentColumns = "assessment_id",
                childColumns = "assessment_reference",
                onUpdate = CASCADE,
                onDelete = CASCADE
        )
})
public class ObjectiveAssessment extends Assessment {

    private long assessment_reference;
    private String type;

    public ObjectiveAssessment(long assessment_id, String assessmentName, String assessmentStart, String assessmentEnd, long courseID, long assessment_reference, String type) {
        super(assessment_id, assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }

    @Ignore
    public ObjectiveAssessment(String assessmentName, String assessmentStart, String assessmentEnd, long courseID, long assessment_reference, String type) {
        super(assessmentName, assessmentStart, assessmentEnd, courseID);
        this.assessment_reference = assessment_reference;
        this.type = type;
    }

    @Override
    public Long getAssessment_id() {
        return super.getAssessment_id();
    }

    @Override
    public void setAssessment_id(Long assessment_id) {
        super.setAssessment_id(assessment_id);
    }

    @Override
    public String getAssessmentName() {
        return super.getAssessmentName();
    }

    @Override
    public void setAssessmentName(String assessmentName) {
        super.setAssessmentName(assessmentName);
    }

    @Override
    public String getAssessmentStart() {
        return super.getAssessmentStart();
    }

    @Override
    public void setAssessmentStart(String assessmentStart) {
        super.setAssessmentStart(assessmentStart);
    }

    @Override
    public String getAssessmentEnd() {
        return super.getAssessmentEnd();
    }

    @Override
    public void setAssessmentEnd(String assessmentEnd) {
        super.setAssessmentEnd(assessmentEnd);
    }

    @Override
    public long getCourseID() {
        return super.getCourseID();
    }

    @Override
    public void setCourseID(long courseID) {
        super.setCourseID(courseID);
    }

    public long getAssessment_reference() {
        return assessment_reference;
    }

    public void setAssessment_reference(long assessment_reference) {
        this.assessment_reference = assessment_reference;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }
}

A POJO has the Assessment Embedded and the Perf and Obj included via @Relation (not the ideal as if the rule of only 1 or the other than 1 will be null). So :-

AssessmentWithPerformanceAssessmentAndObjectiveAssessment

class AssessmentWithPerformanceAssessmentAndObjectiveAssessment {
    @Embedded
    Assessment assessment;
    @Relation(
            entity = PerformanceAssessment.class,
            parentColumn = "assessment_id",
            entityColumn = "assessment_reference"
    )
    PerformanceAssessment performanceAssessment;
    @Relation(
            entity = ObjectiveAssessment.class,
            parentColumn = "assessment_id",
            entityColumn = "assessment_reference"
    )
    ObjectiveAssessment objectiveAssessment;
}

A single @Dao annotated class :-

AssessmentDao

@Dao
abstract class AssessmentDao {

    @Insert
    abstract long insert(Assessment assessment);
    @Insert
    abstract long insert(PerformanceAssessment performanceAssessment);
    @Insert
    abstract long insert(ObjectiveAssessment objectiveAssessment);
    /*
        use to check if an Assessment has any children
     */
    @Query("WITH cte_counts(counter) AS (" +
            "SELECT count(*) > 0 FROM performance_assessment WHERE assessment_reference=:assessment_id " +
            "UNION ALL SELECT count(*) FROM objective_assessment WHERE assessment_reference=:assessment_id" +
            ")" +
            "SELECT sum(counter) > 0 FROM cte_counts")
    abstract boolean hasChildAlready(long assessment_id);

    @Transaction
    @Query("SELECT * FROM assessment_table WHERE assessment_id=:assessment_id")
    abstract AssessmentWithPerformanceAssessmentAndObjectiveAssessment getAssessmentWithPerformanceAssessmentOrObjectiveAssessmentById(long assessment_id);

    @Transaction
    @Query("SELECT * FROM assessment_table")
    abstract List<AssessmentWithPerformanceAssessmentAndObjectiveAssessment> getAllAssessmentsWithPerformanceAssessmentOrObjectiveAssessmentById();
}

• note an abstract class rather than an interface (could be an interface though)

An @Database annotated class

AssessmentDatabase

@Database(entities = {Assessment.class,PerformanceAssessment.class,ObjectiveAssessment.class},version =1)
abstract class AssessmentDatabase extends RoomDatabase {
    abstract AssessmentDao getAssessmentDao();

    private static volatile AssessmentDatabase instance = null;
    static AssessmentDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context,AssessmentDatabase.class,"assessment.db")
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

• note for convenience and brevity allows running on the main thread.

Finally actually using the above in an Activity:-

public class MainActivity extends AppCompatActivity {

    AssessmentDatabase db;
    AssessmentDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = AssessmentDatabase.getInstance(this);
        dao = db.getAssessmentDao();

        /* Various Inserts */
        long loneAssessment = dao.insert(
                new Assessment(
                "Lone Assessment with no children",
                "2021-01-15",
                "2021-03-15",
                0L
                )
        );
        /* id will be 100, subsequent id's if generated will be 101,102......*/
        long a1 = dao.insert(new Assessment(100,"A1","2022-01-01","2022,03-01",10));
        long p1 = dao.insert(new PerformanceAssessment("P1","2022-01-31","2022-01-31",10,a1,"X"));

        /* back to using generated Assessment id */
        long a2 = dao.insert(new Assessment("A1","2022-01-01","2022-03-01",11));
        long p2 = dao.insert(new ObjectiveAssessment("O1","2022-01-31","2022-01-31",11,a2,"Y"));

        /* Build the Assessment and ObjectiveAssessment ready for  insert SEE WARNING */
        Assessment a10 = new Assessment("A10","2022-01-01","2022-03-01",20);
        ObjectiveAssessment o10 = new ObjectiveAssessment("O10","2022-01-31","2022-01-31",a10.getCourseID(),0,"Z");
        /* WARNING assessment_reference WILL NOT BE ANY GOOD (will be 0) */
        long a10id = dao.insert(a10);
        o10.setAssessment_reference(a10id); /*<<<<<<<<<< NOW assessment_reference should be good */
        dao.insert(o10);

        /* Both together */
        dao.insert(
                new PerformanceAssessment("P20","2022-05-07","2022-05-07",11,
                        /* get the Assessment ID from the insert of the Assessment */
                        dao.insert(
                                new Assessment("A20","2022-04-01","2022-06-17",21)
                        ),
                        "ZZ"
                )
        );

        /* Extract all the Assessments with their children (if any)*/
        for(AssessmentWithPerformanceAssessmentAndObjectiveAssessment awpoo: dao.getAllAssessmentsWithPerformanceAssessmentOrObjectiveAssessmentById()) {
            Log.d("DBINFO","Assessment is " + awpoo.assessment.getAssessmentName() + " id is " + awpoo.assessment.getAssessment_id() + " etc....");
            /* need to check if the the performanceassessment is null - it will be if there isn't one */
            if (awpoo.performanceAssessment != null) {
                Log.d("DBINFO","\t\tPerformanceAssessment = " + awpoo.performanceAssessment.getAssessmentName() +
                        " id is "+ awpoo.performanceAssessment.getAssessment_id() +
                        " references Asessment with id " + awpoo.performanceAssessment.getAssessment_reference());
            } else {
                Log.d("DBINFO","\t\tNo PerformanceAssessment.");
            }
            /* null check see performance assessment check above */
            if (awpoo.objectiveAssessment != null) {
                Log.d("DBINFO","\t\tObjectiveAssessment = " + awpoo.objectiveAssessment.getAssessmentName() +
                        " id is "+ awpoo.objectiveAssessment.getAssessment_id() +
                        " references Asessment with id " + awpoo.objectiveAssessment.getAssessment_reference());
            } else {
                Log.d("DBINFO","\t\tNo ObjectiveAssessment.");
            }
        }
    }
}

When run (first time, will fail if run twice due to use of 100 for the id) the log shows :-

D/DBINFO: Assessment is Lone Assessment with no children id is 1 etc....
D/DBINFO:       No PerformanceAssessment.
D/DBINFO:       No ObjectiveAssessment.
D/DBINFO: Assessment is A1 id is 100 etc....
D/DBINFO:       PerformanceAssessment = P1 id is 1 references Asessment with id 100
D/DBINFO:       No ObjectiveAssessment.
D/DBINFO: Assessment is A1 id is 101 etc....
D/DBINFO:       No PerformanceAssessment.
D/DBINFO:       ObjectiveAssessment = O1 id is 1 references Asessment with id 101
D/DBINFO: Assessment is A10 id is 102 etc....
D/DBINFO:       No PerformanceAssessment.
D/DBINFO:       ObjectiveAssessment = O10 id is 2 references Asessment with id 102
D/DBINFO: Assessment is A20 id is 103 etc....
D/DBINFO:       PerformanceAssessment = P20 id is 2 references Asessment with id 103
D/DBINFO:       No ObjectiveAssessment.

using App Inspection then the database looks like :-

• See how PerformanceAssessment with an assessment_id of 2 (it's unqiue row identifier) has an assessment_reference value of 103. This says the the Assessment with an assessment_id of 103 is the parent (or this PerformanceAssessment relates(belongs) to the Assessment that has an assessment_id of 103).