weberdevelopment blog

When you work with databases, you will often have relationships between different tables. This can be achieved by so called “foreign keys”. In my Android application I also wanted to make use of foreign keys, so that if a user deletes for example a subject, he also deletes all the grades assigned to that subject (ON CASCADE). Thus I didn’t had to write a function, which deletes every dependent other element in the database.

You need two steps to achieve foreign keys in Android:

1. Create the table with FOREIGN KEY

private static final String DB_CREATE_SUBJECTS =
	    "CREATE TABLE table_subjects " +
	    "(_id INTEGER PRIMARY KEY AUTOINCREMENT, " +
	    "title TEXT NOT NULL," +
	    "term_id INTEGER NOT NULL," +
	    "FOREIGN KEY(term_id) REFERENCES table_terms(_id) ON DELETE CASCADE" +
	    ");";

The key line here is “FOREIGN KEY(term_id) REFERENCES table_terms(_id) ON DELETE CASCADE”
It tells that the term_id of this table is a foreign key, which refers to the _id of table_terms. Consequently if we delete a term_id (id = 2), all referenced subjects with the term_id = 2, will be deleted too.

Actually I thought that was all, but while testing, I remarked that the referenced elements where not deleted. On stackoverflow I found the following solution:

    // Enable foreign key constraints
    if (!db.isReadOnly()) {
        db.execSQL("PRAGMA foreign_keys = ON;");
    }

Allegedly foreign_keys have to be “turned on”. I put this piece of code in my “DbAdapter open()” method and it worked:

public DbAdapter open() throws SQLException {
        mDbHelper = new DBManager(mCtx);
        db = mDbHelper.getWritableDatabase();
 
        // Enable foreign key constraints
        if (!db.isReadOnly()) {
        	db.execSQL("PRAGMA foreign_keys = ON;");
        }
        return this;
    }