weberdevelopment blog

Android with foreign keys in sqlite

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;
    }