Foreign key constraints in Android using SQLite? on Delete cascade

android, foreign-keys, java, sqlite

Solution

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

Problem

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" + " );" ); ... } ```

Original source