Referential Integirty with Android Sqlite?

If you look for documentation about sqlite, you will most quickly find all the excellent documentation on the website. And after a while you will find out that the documentation is for the latest version, which is currently 3.7.0.1. However when you play around with sqlite on the Android emulator or phone you will find that e.g. Android 1.5, 1.6 and 2.1 include SQLite 3.5.9 and only Android 2.2 includes the newer SQLite 3.6.22. Now looking up foreign key support on the documentation shows that it foreign key support was only added with version 3.6.19 and that it is disabled by default even with higher versions.

If you are  supporting Android 2.1 or below you are out of luck with this foreign key support and you will have to look for an alternative. Luckily there is one, so lets see. First we have our tables created like this:

CREATE TABLE ant (id INTEGER NOT NULL PRIMARY KEY, anthillid INTEGER, name TEXT, gender TEXT);

CREATE TABLE anthill (id INTEGER NOT NULL PRIMARY KEY, name TEXT, species TEXT, longitude TEXT, latitude TEXT);

Note that the creation statements above do not create any constraints. We could add that and SQLite supports the syntax, but it does not enforce it. So I leave it out to be more explicit.Now in order to be able to delete an anthill with a query like 

DELETE FROM anthill WHERE id ='5';

without having to delete the individual ants or even just issue a batch delete explicitly in Java code, we take advantage of triggers. It so happens that SQLite supports triggers and you can set up a trigger that will do the deletion automatically like this

CREATE TRIGGER delete_ants_with_anthill<br>

BEFORE DELETE ON anthill<br>

FOR EACH ROW BEGIN<br>

    DELETE FROM ant WHERE ant.anthillid = OLD.id;<br>

END;

In a similar manner you can enforce integrity upon insert and update allowing you to not worry about inconsistent data in the database and handling edge cases in your Java code. Overall this approach will allow you to reduce the complexity of your Java code as well as improve your overall performance. Not a bad deal I would say..

Don’t forget you will need to include turning them on in your DB Helper class:

@Override
public void onOpen(SQLiteDatabase db) {
super.onOpen(db);
if (!db.isReadOnly())
     {
        db.execSQL(“PRAGMA foreign_keys=ON;”);
      }
}