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

    ...
}

score 238 · Accepted Answer · edited Jan 31 '12 at 13:19

238

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

edited Jan 31 '12 at 13:19

CSchulz

10,882
11
60
114

answered Jul 16 '10 at 16:15

Phil

4,377
3
21
10

59

Which means it only works since Android 2.2 Froyo which has SQLite 3.6.22 – Intrications Jan 24 '11 at 12:13
@RedPlanet - it's because the only time this constraint is enforced is when something is written to the database. (You can't break this constraint if all you do is read from the db) Also, Phil, instead of the onOpen method, it's probably better to do it in the onConfigure method. Source: http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#onConfigure(android.database.sqlite.SQLiteDatabase) – ElectronAnt Jun 26 '13 at 20:19
13

Google recommends writting `PRAGMA` statements in [`onConfigure()`](http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#onConfigure(android.database.sqlite.SQLiteDatabase)) but it requires API level 16 (Android 4.1), and by then you can simply call [`setForeignKeyConstraintsEnabled`](http://stackoverflow.com/a/12531927/1402846). – Pang Aug 26 '13 at 02:45
One may also need to consider enabling foreign key constraints in `onCreate`/`onDowngrade`/`onUpgrade`, which are before `onOpen`. See [source code in Android 4.1.1](http://grepcode.com/file/repository.grepcode.com/java/ext/com.google.android/android/4.1.1_r1/android/database/sqlite/SQLiteOpenHelper.java/#240). – Pang Aug 26 '13 at 02:52
You don't need to call super, the default implementation is empty. – Natix Oct 15 '14 at 21:38
@Intrications I have created tables having foreign key but i haven't enable them using Pragma command and my tables are created successfully! – Muhammad Babar Feb 05 '15 at 18:36
1

@Natix including the call to super ensures correct functionality if an intermediate class is introduced between the implemented class and its parent. – tbm May 17 '15 at 20:14

score 56 · Answer 2 · edited Aug 27 '15 at 20:17

56

Since Android 4.1 (API 16) SQLiteDatabase supports:

public void setForeignKeyConstraintsEnabled (boolean enable)

edited Aug 27 '15 at 20:17

Alexander Farber

21,519
75
241
416

answered Sep 21 '12 at 13:52

e.shishkin

1,173
12
9

score 27 · Answer 3 · edited Aug 26 '15 at 07:23

27

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);
}

edited Aug 26 '15 at 07:23

Sergio del Amo

76,835
68
152
179

answered Dec 22 '13 at 15:19

malcolm

271
3
2

score 10 · Answer 4 · answered Oct 26 '16 at 02:10

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);
}

score 6 · Answer 5 · edited Sep 18 '15 at 19:33

6

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

edited Sep 18 '15 at 19:33

Vikram Bodicherla

7,133
4
28
34

answered Aug 18 '15 at 13:20

anand krish

4,281
4
44
47

3

The documentation you posted suggests: `A good time to call this method is right after calling openOrCreateDatabase(File, SQLiteDatabase.CursorFactory) or in the onConfigure(SQLiteDatabase) callback.` So instead of `onOpen`, `onConfigure` seems to be the right place. – Paul Woitaschek Jun 02 '17 at 13:47

score 4 · Answer 6 · answered Mar 30 '10 at 14:13

4

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

Create transaction
Delete detail data (waypoints in your example)
Delete master data (tracks in your example)
Commit transaction on success

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

answered Mar 30 '10 at 14:13

Thorsten Dittmar

55,956
8
91
139

But are you deleting from both tables using one method? – jcrowson Mar 30 '10 at 14:31
Yes, I went pretty much along with the Notes sample from the API. When I am to delete what would be a track in your case, I create the transaction, delete track and waypoints and commit the transaction. That's all in one go. – Thorsten Dittmar Mar 30 '10 at 14:47

score 4 · Answer 7 · answered Mar 30 '10 at 14:55

4

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.

answered Mar 30 '10 at 14:55

Dave.B

6,632
1
19
20

score 3 · Answer 8 · edited Oct 20 '11 at 21:51

3

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 -version

edited Oct 20 '11 at 21:51

scorpiodawg

5,612
3
42
62

answered Aug 04 '10 at 09:38

GBouerat

480
3
13

So is there any way to forces such constraints.. I mean is there any way to upgrade sqlite version.. because we must have to support out software version to android 2.1 which has sqlite version 3.5.9 as above – NullPointerException May 23 '12 at 12:11
No, you have to handle everything by yourself :( – GBouerat May 29 '12 at 15:35

score 1 · Answer 9 · answered Sep 01 '11 at 10:29

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.

score 0 · Answer 10 · answered Mar 26 '20 at 17:15

If you are using Android Room, do as shown below.

Room.databaseBuilder(context, AppDatabase::class.java, DATABASE_NAME)
    .addCallback(object : RoomDatabase.Callback() {
        // Called when the database has been opened.
        override fun onOpen(db: SupportSQLiteDatabase) {
            super.onOpen(db)
            //True to enable foreign key constraints
            db.setForeignKeyConstraintsEnabled(true)
        }

        // Called when the database is created for the first time. 
        override fun onCreate(db: SupportSQLiteDatabase) {
            super.onCreate(db)
        }
    }).build()

Foreign key constraints in Android using SQLite? on Delete cascade

10 Answers10

Linked

Related