Referential integrity with sqlite on Android the lazy way

As you all know the main supported persistence mechanism on Android is storing data in the extremely widespread embedded database SQLite. Now when you look around various books and documentation, the examples you get are most of the time are pretty trivial. When you then come from a background of full on relational database management systems like PostgreSQL, working with SQLite with have a few surprises to you. One of those surprises is that referential integrity is not a standard feature unless you have some tricks up your sleave..

Before we delve too deep into technical details lets see what we actually want to achieve. In our fictional example we are going to have a database of ants that all belong to their own anthill. We will also have an anteater that can wipe out a whole colony in one super power swoop. In order to implement this one action deletion of the anthill easily we would have to access the database and find all ant records that belong to the affected anthill and delete all those before we delete the anthill itself. Now ideally this could just happen automatically by getting the anteater to wipe out the anthill. So how do we go about that?

In the normal way without this trick you would have to issue one batch delete statement for all the ants of a particular anthill first and then another one for the actual hill from your Java code, but there is a better way…

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:

<br>
CREATE TABLE ant (id INTEGER NOT NULL PRIMARY KEY, anthillid INTEGER, name TEXT, gender TEXT);<br>
CREATE TABLE anthill (id INTEGER NOT NULL PRIMARY KEY, name TEXT, species TEXT, longitude TEXT, latitude TEXT);<br>

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 

<br>
DELETE FROM anthill WHERE id ='5';<br>

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

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

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..

PS: Not ants were killed during the write up of the blog post.
PPS: The data model does not support army ants.