Foreign key constraints in Android using SQLite? o

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

Foreign keys with "on delete cascade" are supported in SQLite in Android 2.2 and up. But be careful when using them: sometimes an error is reported when firing up one foreign key on one column, but the real problem lies in either another column foreign key constraint in the child table, or some other table thet references this table.

Looks like SQLite checks all constraints when firing up one of them. It is actually mentioned in the documentation. DDL versus DML constraint checks.

Answer 2

Since Android 4.1 (API 16) SQLiteDatabase supports:

public void setForeignKeyConstraintsEnabled (boolean enable)

Answer 3

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 4

Never too old of a question to answer with a more complete answer.

@Override public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    if (!db.isReadOnly()) {
        setForeignKeyConstraintsEnabled(db);
    }
    mOpenHelperCallbacks.onOpen(mContext, db);
}

private void setForeignKeyConstraintsEnabled(SQLiteDatabase db) {
    if (Build.VERSION.SDK_INT < Build.VERSION_CODES.JELLY_BEAN) {
        setForeignKeyConstraintsEnabledPreJellyBean(db);
    } else {
        setForeignKeyConstraintsEnabledPostJellyBean(db);
    }
}

private void setForeignKeyConstraintsEnabledPreJellyBean(SQLiteDatabase db) {
    db.execSQL("PRAGMA foreign_keys=ON;");
}

@TargetApi(Build.VERSION_CODES.JELLY_BEAN)
private void setForeignKeyConstraintsEnabledPostJellyBean(SQLiteDatabase db) {
    db.setForeignKeyConstraintsEnabled(true);
}

Answer 5

Whatever @phil mentioned is good. But you can use another default method available in Database itself to set the foreignkey. That is setForeignKeyConstraintsEnabled(true).

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

For Docs refer SQLiteDatabase.setForeignKeyConstraintsEnabled

Answer 6

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.