Foreign key constraints in Android using SQLite? on Delete cascade.

Question

I have two tables: tracks and waypoints, a track can have many waypoints, but a waypoint is assigned to only 1 track.

In the way points table I have a column called "trackidfk" which inserts the track_ID once a track is made, however I have not setup Foreign Key constraints on this column.

When I delete a track I want to delete the assigned waypoints, is this possible?. I read about using Triggers but I don't think they are supported in Android.

To create the waypoints table:

public void onCreate(SQLiteDatabase db) { 
      db.execSQL("CREATE TABLE " + TABLE_NAME + " (" + _ID
            + " INTEGER PRIMARY KEY AUTOINCREMENT, " + LONGITUDE
            + " INTEGER," + LATITUDE + "  INTEGER," + TIME
            + " INTEGER,"+ TRACK_ID_FK + " INTEGER );");

Answer 1

I don't think SQLite supports this out of the box. What I'm doing in my apps is:

1. Create transaction
2. Delete detail data (waypoints in your example)
3. Delete master data (tracks in your example)
4. Commit transaction on success

That way I'm sure that either all the data is deleted or none.

Answer 2

Triggers are supported by android and that type of cascade delete is not supported by sqlite. An example of using triggers on android can be found here. Though using transactions as Thorsten stated is probably just as easy as a trigger.

Answer 3

In the end I decided to hard-code the delete:

private void processDelete(long rowId) {
    String[] args = { String.valueOf(rowId) };
    SQLiteDatabase myDB = null;
    myDB = this.openOrCreateDatabase(MY_DB_NAME, MODE_PRIVATE, null);
    myDB.delete("tracks", "_ID=?", args);
    myDB.delete("waypoints", "trackidfk=?", args);
    cur.requery();
}

Answer 4

Foreign key constraints with on delete cascade are supported, but you need to enable them. I just added the following to my SQLOpenHelper, which seems to do the trick.

 @Override
  public void onOpen(SQLiteDatabase db)
  {
    super.onOpen(db);
    if (!db.isReadOnly())
    {
      // Enable foreign key constraints
      db.execSQL("PRAGMA foreign_keys=ON;");
    }
  }

I declared my referencing column as follows.

mailbox_id INTEGER REFERENCES mailboxes ON DELETE CASCADE

Answer 5

Excelent answer by Phil

Thanks!!

N.B. Don't forget non-SQLite providers.

( can't vote yet :-( )

Answer 6

SQLite version in android 1.6 is 3.5.9 so it doesn't support foreign keys...

http://www.sqlite.org/foreignkeys.html "This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19."

In Froyo it's SQLite version 3.6.22, so ...

EDIT: to see sqlite version : adb shell sqlite3