Creating foreign key constraints in ORMLite under SQLite

Question

As it is not possible to add foreign keys using an "ALTER TABLE" statement in SQLite, I am stuck on how to configure my database to enforce valid foreign keys, or perform cascaded deletes without explicit code overhead.

Anybody got an idea how to accomplish this with ORMLite under SQLite?

Answer 1

To elaborate on Gray's awesome answer (for anyone else who stumbles upon this question), you can use the columnDefinition annotation to define a foreign key constraint and cascading delete.

First, foreign key constraints were added to SQLite in 3.6.19, which means you can use them in Android 2.2 or higher (since 2.2 ships with SQLite 3.6.22). However, foreign key constraints are not enabled by default. To enable them, use the technique from this answer.

Here's an example using the columnDefinition annotation. This assumes your table/object you are referencing is called parent which has a primary key of id.

@DatabaseField(foreign = true,
      columnDefinition = "integer references parent(id) on delete cascade")
private Parent parent;

Note that the format for the String value does not include the column name (that's what the columnName annotation is for).