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

    ...
}

CSchulz · Accepted Answer · 2012-01-31 13:19:02Z

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, 2012 at 13:19

CSchulz

11.1k11 gold badges63 silver badges118 bronze badges

answered Jul 16, 2010 at 16:15

Phil

4,4073 gold badges24 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Intrications Over a year ago

Which means it only works since Android 2.2 Froyo which has SQLite 3.6.22

ElectronAnt Over a year ago

@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: developer.android.com/reference/android/database/sqlite/…

Pang Over a year ago

Google recommends writting PRAGMA statements in onConfigure() but it requires API level 16 (Android 4.1), and by then you can simply call setForeignKeyConstraintsEnabled.

Pang Over a year ago

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.

tbm Over a year ago

@Natix including the call to super ensures correct functionality if an intermediate class is introduced between the implemented class and its parent.

|

Alexander Farber · Accepted Answer · 2015-08-27 20:17:39Z

56

Since Android 4.1 (API 16) SQLiteDatabase supports:

public void setForeignKeyConstraintsEnabled (boolean enable)

edited Aug 27, 2015 at 20:17

Alexander Farber

23.4k79 gold badges263 silver badges449 bronze badges

answered Sep 21, 2012 at 13:52

e.shishkin

1,19312 silver badges9 bronze badges

Comments

Sergio del Amo · Accepted Answer · 2015-08-26 07:23:08Z

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, 2015 at 7:23

Sergio del Amo

78.3k68 gold badges154 silver badges180 bronze badges

answered Dec 22, 2013 at 15:19

malcolm

2713 silver badges2 bronze badges

Comments

Codeversed · Accepted Answer · 2016-10-26 02:10:38Z

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

Vikram Bodicherla · Accepted Answer · 2015-09-18 19:33:05Z

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, 2015 at 19:33

Vikram Bodicherla

7,1334 gold badges30 silver badges34 bronze badges

answered Aug 18, 2015 at 13:20

anand krish

4,4355 gold badges47 silver badges49 bronze badges

1 Comment

Paul Woitaschek Over a year ago

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.

Thorsten Dittmar · Accepted Answer · 2010-03-30 14:13:43Z

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, 2010 at 14:13

Thorsten Dittmar

56.9k10 gold badges95 silver badges145 bronze badges

2 Comments

jcrowson Over a year ago

But are you deleting from both tables using one method?

Thorsten Dittmar Over a year ago

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.

Dave.B · Accepted Answer · 2010-03-30 14:55:25Z

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, 2010 at 14:55

Dave.B

6,6621 gold badge21 silver badges20 bronze badges

Comments

scorpiodawg · Accepted Answer · 2011-10-20 21:51:00Z

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, 2011 at 21:51

scorpiodawg

5,7623 gold badges45 silver badges62 bronze badges

answered Aug 4, 2010 at 9:38

GBouerat

4803 silver badges13 bronze badges

2 Comments

NullPointerException Over a year ago

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

GBouerat Over a year ago

No, you have to handle everything by yourself :(

Yar · Accepted Answer · 2011-09-01 10:29:11Z

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.

answered Sep 1, 2011 at 10:29

Yar

4,5712 gold badges37 silver badges43 bronze badges

Comments

krishnakumarp · Accepted Answer · 2020-03-26 17:15:19Z

0

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()

answered Mar 26, 2020 at 17:15

krishnakumarp

9,3254 gold badges53 silver badges58 bronze badges

Collectives™ on Stack Overflow

Foreign key constraints in Android using SQLite? on Delete cascade

10 Answers 10

7 Comments

Comments

Comments

Comments

1 Comment

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

10 Answers 10

7 Comments

Comments

Comments

Comments

1 Comment

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related