Trouble with SQLite foreign key syntax using listviews to retrieve data

Question

This is the context. I have a databasehelper that creates 3 tables in one database. Two of those tables have foreign keys. Everything worked it was all good but given what I wanted to do I needed to use listviews instead of textviews to display my data and be able to edit, delete etc. For some reason listviews are a different animal on Android than other programming languages. I figured out adapters and all that nonsense. My new problem was that My app wouldn't display the data and when I checked my logcat I got this message

 Caused by: java.lang.IllegalArgumentException: column '_id' does not exist. Available columns: [id, termName, termStart, termEnd]

That was for one table named 'term_table'. So I changed the id from 'id' to '_id' and that did the trick.

So everything is all good right? Nope, because the other two tables have their own ID's and foreign keys. When I did the same query for a different table named 'assess_table' I got the same error so I change the 'id' to '_id'. The problem is 'courses_table' (third table) uses '_id' as a foreign key to reference a table called 'term_table'. So I get an error telling me I was creating duplicate columns- the primary key '_id' and the foreign key '_id' to reference term_table.

I rename the id of 'courses_table' from '_id' to 'courses_id'. Now I'm back to square one getting the same error:

 Caused by: java.lang.IllegalArgumentException: column '_id' does not exist. Available columns: [assess_id, assessName, assessDueDate, assessType, courses_id]

So basically if all three tables have to be named _id what syntax am I supposed to use when creating foreign keys if the foreign keys have the same name as the primary key of the referenced table, so I'll always get the creating duplicate columns error. I read that this _id naming scheme is only a thing with cursor adapters. What syntax is appropriate to use?

This is my 3 tables in my databasehelper

//terms table
   public static final String TERM_TABLE_NAME = "term_table";
   public static final String COL_TERM1 = "_id";
   public static final String COL_TERM2 = "termName";
   public static final String COL_TERM3 = "termStart";
   public static final String COL_TERM4 = "termEnd";
   public static final String TERM_DATABASE_NAME = "term.db";
   //courses table
   public static final String COURSES_TABLE_NAME = "courses_table";
   public static final String COL_COURSE1 = "courses_id";
   public static final String COL_COURSE2 = "courseTitle";
   public static final String COL_COURSE3 = "courseStartDate";
   public static final String COL_COURSE4 = "courseEndDate";
   public static final String COL_COURSE5 = "courseStatus";
   public static final String COL_COURSE6 = "optionalNote";
   public static final String COL_COURSE7 = "mentorName";
   public static final String COL_COURSE8 = "mentorPhone";
   public static final String COL_COURSE9 = "mentorEmail";
   public static final String COL_COURSE_TERMID = "_id";
   //assess table
   public static final String ASSESS_TABLE_NAME = "assess_table";
   public static final String COL_ASSESS1 = "assess_id";
   public static final String COL_ASSESS2 = "assessName";
   public static final String COL_ASSESS3 = "assessDueDate";
   public static final String COL_ASSESS4 = "assessType";
   public static final String COL_ASSESS_COURSEID = "courses_id";

this is the create syntax

public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TERM_TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, termName TEXT, termStart TEXT, termEnd TEXT)");
        db.execSQL("CREATE TABLE " + COURSES_TABLE_NAME + " (courses_id INTEGER PRIMARY KEY AUTOINCREMENT, courseTitle TEXT, courseStartDate TEXT, courseEndDate TEXT, courseStatus INTEGER, optionalNote TEXT, mentorName TEXT, mentorPhone TEXT, mentorEmail TEXT, _id INTEGER, FOREIGN KEY (_id) REFERENCES term_table(_id))");
        db.execSQL("CREATE TABLE " + ASSESS_TABLE_NAME + " (assess_id INTEGER PRIMARY KEY AUTOINCREMENT, assessName TEXT, assessDueDate TEXT, assessType INTEGER, courses_id INTEGER, FOREIGN KEY (courses_id) REFERENCES courses_table(courses_id))");
    }

A FK is a list of columns that references a list of columns. The nth referencing column does not have to have the same name as the nth referenced column. PS Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. Give code that causes the original error--is that what the code you give does? — philipxy, Apr 03 '19 at 23:43
[About “_id” field in Android SQLite](https://stackoverflow.com/q/3192064/3404097) — philipxy, Apr 04 '19 at 00:42

ygngy · Answer 1 · 2019-04-04T00:42:14.693

rename primary and foreign keys like this:

public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TERM_TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, termName TEXT, termStart TEXT, termEnd TEXT)");
        db.execSQL("CREATE TABLE " + COURSES_TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, courseTitle TEXT, courseStartDate TEXT, courseEndDate TEXT, courseStatus INTEGER, optionalNote TEXT, mentorName TEXT, mentorPhone TEXT, mentorEmail TEXT, for_cors_term INTEGER, FOREIGN KEY (for_cors_term) REFERENCES term_table(_id))");
        db.execSQL("CREATE TABLE " + ASSESS_TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, assessName TEXT, assessDueDate TEXT, assessType INTEGER, for_ases_cors INTEGER, FOREIGN KEY (for_ases_cors) REFERENCES courses_table(_id))");
    }

   public static final String TERM_DATABASE_NAME = "term.db";

   //terms table
   public static final String TERM_TABLE_NAME = "term_table";
   public static final String COL_TERM1 = "_id"; //primary key
   public static final String COL_TERM2 = "termName";
   public static final String COL_TERM3 = "termStart";
   public static final String COL_TERM4 = "termEnd";

   //courses table
   public static final String COURSES_TABLE_NAME = "courses_table";
   public static final String COL_COURSE1 = "_id"; //primary key
   public static final String COL_COURSE2 = "courseTitle";
   public static final String COL_COURSE3 = "courseStartDate";
   public static final String COL_COURSE4 = "courseEndDate";
   public static final String COL_COURSE5 = "courseStatus";
   public static final String COL_COURSE6 = "optionalNote";
   public static final String COL_COURSE7 = "mentorName";
   public static final String COL_COURSE8 = "mentorPhone";
   public static final String COL_COURSE9 = "mentorEmail";
   public static final String COL_COURSE_TERMID = "for_cors_term"; //foreign key

   //assess table
   public static final String ASSESS_TABLE_NAME = "assess_table";
   public static final String COL_ASSESS1 = "_id"; //primary key
   public static final String COL_ASSESS2 = "assessName";
   public static final String COL_ASSESS3 = "assessDueDate";
   public static final String COL_ASSESS4 = "assessType";
   public static final String COL_ASSESS_COURSEID = "for_ases_cors"; //foreign key

Or if you do not want to use _id field as primary key you can use RecyclerView instead of ListView

MikeT · Answer 2 · 2019-04-04T04:18:50.950

So basically if all three tables have to be named _id what syntax am I supposed to use when creating foreign keys if the foreign keys have the same name as the primary key of the referenced table, so I'll always get the creating duplicate columns error.

They don't have to be as you could use AS _id to dynamically generate column with an alias (note due to * you have id and also _id column) e.g.

SELECT *, id AS _id

you'd only ever want the 1 _id column per CursorAdapter.

As for defining the Foreign Key using

INTEGER PRIMARY KEY _id,....., FOREIGN KEY(_id) REFERENCES parent_table(column_in_parent_table)

it is probably NOT going to work consistently/well/usefully.

You are effectively saying that the _id column must be a value in the parent table. Thus you are restricting the relationship to a 1-1 relationship as INTEGER PRIMARY KEY is implicitly unique as it's an alias of the normally hidden rowid column, the value, as the column is special, MUST also be an integer.

e.g. if the first row of the courses table references the column with a value of 1, then fine (say TERM1), then you cannot have another reference to TERM1, in that table as the _id MUST not be 1.

Thus the unique constraint (duplicates) you encountered.

In short you should reference an _id column as the parent table column of a foreign key (in your case), but not use the _id column in the child table as the column that references the parent table, as you then restrict the parent to having just the one child for that value, a 1-1 relationship (which effectively means that the two tables are redundant).

Instead the column in the child table should be a column primarily for storing the reference/relationship/association (which may be a value that has other uses).

As such you want something like :-

public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TERM_TABLE_NAME + " (_id INTEGER PRIMARY KEY, termName TEXT, termStart TEXT, termEnd TEXT)");
        db.execSQL("CREATE TABLE " + COURSES_TABLE_NAME + " (_id INTEGER PRIMARY KEY, courseTitle TEXT, courseStartDate TEXT, courseEndDate TEXT, courseStatus INTEGER, optionalNote TEXT, mentorName TEXT, mentorPhone TEXT, mentorEmail TEXT, course_term_reference INTEGER REFERENCES term_table(_id))");
        db.execSQL("CREATE TABLE " + ASSESS_TABLE_NAME + " (_id INTEGER PRIMARY KEY, assessName TEXT, assessDueDate TEXT, assessType INTEGER, assess_course_reference INTEGER REFERENCES courses_table(_id))");
}

Note AUTOINCREMENT has been removed, you don't need this nor the overheads it incurs SQLite Autoincrement as per :-

The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.

The simpler and perhaps more easily understood column foreign key definitions have been used (i.e. the foreign keys have been defined at the column definition level rather than the table definition level)

However, I'd suggest coding identifiers (column names, table names etc) once as constants and then always referring to those constants.

As such I'd suggest the above would be better as :-

public static final String TERM_TABLE_NAME = "term_table";
public static final String COL_TERM1 = "_id";
public static final String COL_TERM2 = "termName";
public static final String COL_TERM3 = "termStart";
public static final String COL_TERM4 = "termEnd";
public static final String TERM_DATABASE_NAME = "term.db";
public static final int DBVERSION = 1;
//courses table
public static final String COURSES_TABLE_NAME = "courses_table";
public static final String COL_COURSE1 = "_id";
public static final String COL_COURSE2 = "courseTitle";
public static final String COL_COURSE3 = "courseStartDate";
public static final String COL_COURSE4 = "courseEndDate";
public static final String COL_COURSE5 = "courseStatus";
public static final String COL_COURSE6 = "optionalNote";
public static final String COL_COURSE7 = "mentorName";
public static final String COL_COURSE8 = "mentorPhone";
public static final String COL_COURSE9 = "mentorEmail";
public static final String COL_COURSE_TERM_REFERENCE = "term_reference";
//assess table
public static final String ASSESS_TABLE_NAME = "assess_table";
public static final String COL_ASSESS1 = "_id";
public static final String COL_ASSESS2 = "assessName";
public static final String COL_ASSESS3 = "assessDueDate";
public static final String COL_ASSESS4 = "assessType";
public static final String COL_ASSESS_COURSE_REFERENCE = "course_reference";

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(
            "CREATE TABLE " + TERM_TABLE_NAME + "(" +
                    COL_TERM1 + " INTEGER PRIMARY KEY," +
                    COL_TERM2 + " TEXT, " +
                    COL_TERM3 + " TEXT, " +
                    COL_TERM4 + " TEXT" +
                    ")"
    );
    db.execSQL(
            "CREATE TABLE " + COURSES_TABLE_NAME + "(" +
                    COL_COURSE1 + " INTEGER PRIMARY KEY, " +
                    COL_COURSE2 + " TEXT, " +
                    COL_COURSE3 + " TEXT, " +
                    COL_COURSE4 + " TEXT, " +
                    COL_COURSE5 + " INTEGER, " +
                    COL_COURSE6 + " TEXT, " +
                    COL_COURSE7 + " TEXT, " +
                    COL_COURSE8 + " TEXT, " +
                    COL_COURSE9 + " TEXT, " +
                    COL_COURSE_TERM_REFERENCE + "INTEGER REFERENCES " + TERM_TABLE_NAME + "(" + COL_TERM1 + ")" +
                    ")"
    );

    db.execSQL(
            "CREATE TABLE " + ASSESS_TABLE_NAME + "(" +
                    COL_ASSESS1 + " INTEGER PRIMARY KEY, " +
                    COL_ASSESS2 + " TEXT, " +
                    COL_ASSESS3 + " TEXT, " +
                    COL_ASSESS4 + " INTEGER, " +
                    COL_ASSESS_COURSE_REFERENCE + " INTEGER REFERENCES " + COURSES_TABLE_NAME + "(" + COL_COURSE1 + ")" +
                    ")"
    );
}

The above results in the SQL (for each table) as being :-

CREATE TABLE term_table(_id INTEGER PRIMARY KEY,termName TEXT, termStart TEXT, termEnd TEXT)
CREATE TABLE courses_table(courses_id INTEGER PRIMARY KEY, courseTitle TEXT, courseStartDate TEXT, courseEndDate TEXT, courseStatus INTEGER, optionalNote TEXT, mentorName TEXT, mentorPhone TEXT, mentorEmail TEXT, term_reference INTEGER REFERENCES term_table(_id))
CREATE TABLE assess_table(assess_id INTEGER PRIMARY KEY, assessName TEXT, assessDueDate TEXT, assessType INTEGER, course_reference INTEGER REFERENCES courses_table(courses_id))

Additional

To utilise foreign key support, you need to turn it on. As such you could override the onConfigure method of the database helper to enable support e.g. :-

@Override
public void onConfigure(SQLiteDatabase db) {
    super.onConfigure(db);
    db.setForeignKeyConstraintsEnabled(true);
}

Trouble with SQLite foreign key syntax using listviews to retrieve data

2 Answers2

Additional