CODEWITHSUNDEEP
androidsqliteandroid-sqlite
Dan Is Fiddling By Firelight
Dan Is Fiddling By Firelight

Reputation: 6061

Cascading delete across multiple tables in android/sqlite

I'm trying to cascade deletes so that when I delete a record from myFooTable all the associated records in myFooBarLinkTable and myBarTable are deleted as well. A simplified version of my current DB schema attempt is below. When I delete a record from myFooTable the associated records in myFooBarTable are deleted; but the records in myBarTable are still present.

public class MyDatabaseManager extends SQLiteOpenHelper {
@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL("CREATE TABLE myFooTable (fooID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "fooText1 TEXT," +
                "fooText2 TEXT " +    
                ");");

    db.execSQL("CREATE TABLE myBarTable (barID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "barText1 TEXT, " +
                "barText2 TEXT);" );

    db.execSQL("CREATE TABLE myFooBarLinkTable (fooBarLinkID INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "fooLinkID REFERENCES myFooTable(fooID) ON DELETE CASCADE, " +
                "barLinkID REFERENCES myBarTable(barID) ON DELETE CASCADE);" );

Upvotes: 1

Views: 880

Answers (1)

CL.
CL.

Reputation: 180020

ON DELETE CASCADE works only one way; these clauses in the myFooBarLinkTable table definition trigger only when a record in the referenced table is deleted, i.e., in either myFooTable or myBarTable.

If you want the deletion to happen in the other way, you have to write your own trigger, or execute separate DELETE commands from your app.

Upvotes: 1

Related Questions