Foreign key constraints in Android using SQLite? on Delete cascade
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"
+ " );"
);
...
}
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
Since Android 4.1 (API 16) SQLiteDatabase supports:
public void setForeignKeyConstraintsEnabled (boolean enable)
As the post from e.shishkin says from API 16 up you should enable foreign key constraints in the SqLiteOpenHelper.onConfigure(SqLiteDatabase)
method using the db.setForeignKeyConstraintsEnabled(boolean)
@Override
public void onConfigure(SQLiteDatabase db){
db.setForeignKeyConstraintsEnabled(true);
}
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);
}