Android On Delete Cascade not working properly

Question

I am making android app in which i make 5 tables in one open helper class. i am facing the problem while i am having ON DELETE CASCADE in my schema. i know the concept of ON DELETE CASCADE. here i am copy my one of two tables.

private static final String MEDICINE_TABLE  = "create table medicine_details (_mid integer primary key autoincrement, "
                                                    + " medicine_name text, type text, take_with text, m_did integer not null,"
                                                    + "FOREIGN KEY(m_did) REFERENCES doctor_details(did)"
                                                    + " ON DELETE CASCADE" + ");";

private static final String SCH_TAB         = "CREATE TABLE schedule_details(sid INTEGER PRIMARY KEY AUTOINCREMENT, "
                                                    + "medi_stdate TEXT,medi_end_date TEXT,time_sch TEXT,rept TEXT, alarm_id NUMBER, "
                                                    + "u_id integer not null, doc_id integer not null, mid integer not null, "
                                                    + "FOREIGN KEY(u_id) REFERENCES member_details(_id)"
                                                    + " ON DELETE CASCADE,"
                                                    + "FOREIGN KEY(doc_id) REFERENCES doctor_details(did)"
                                                    + " ON DELETE CASCADE,"
                                                    + "FOREIGN KEY(mid) REFERENCES medicine_details(_mid)"
                                                    + " ON DELETE CASCADE" + ");";

whenever i try to delete from medicine,it deletes only from medicine table not from schedule table. here medicine table is a master table and schedule table is child table.

What android version are you using? Foreign key support was added in SQLite 3.6.19+ which was first shipped with Android 2.2 Froyo. — Paweł Wyrwiński, Apr 18 '13 at 11:20
Are you sure? It sounds quite unlikely to me. As far as I know Android 4.2 has "only" sqlite 3.7.11. Are you referring to sqlite embedded in your device/emulator or the one you are using on you development PC to prepare database file in "offline" fashion? — Paweł Wyrwiński, Apr 18 '13 at 11:34
when i see my database in firefox with SQL manager tool it gives me this SQLite version, so tell me how can i check my SQLite version? — Riddhi Shah, Apr 18 '13 at 11:38
see this http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android i am using 4.0.3 — Riddhi Shah, Apr 18 '13 at 11:39
Firefox plugin you mentioned has it's own sqlite engine/libraries and Android device (or emulator) has it's own sqlite engine/libraries too. Database file format itself is more or less version agnostic (as long as you're using it under control of 3.x database version). To sum it all: what realy matters is **runtime** sqlite engine/library version only. So if you're performing your tests on Android <= 2.1 no functionality depending on foreign keys will work. — Paweł Wyrwiński, Apr 18 '13 at 11:46
[Here](http://stackoverflow.com/questions/10719425/foreign-key-constraint-on-delete-cascade-not-working-in-sqlite-database-on-andro) is description of problem similar to your. On the other hand your issue may be ralated to [this](http://stackoverflow.com/questions/13641250/sqlite-delete-cascade-not-working). — Paweł Wyrwiński, Apr 18 '13 at 11:57

score 4 · Answer 1 · answered Feb 01 '15 at 14:54

4

you must override the method open() as you can see in the following example:

    @Override
public void onOpen(SQLiteDatabase db) {
    super.onOpen(db);
    if (!db.isReadOnly()) {
        db.execSQL("PRAGMA foreign_keys=ON;");
    }
}

best regards

answered Feb 01 '15 at 14:54

Agna JirKon Rx

2,321
2
29
44

score 1 · Answer 2 · answered Apr 18 '13 at 17:24

1

In your database open() method add the following code
mDb.execSQL("PRAGMA foreign_keys=ON;");

answered Apr 18 '13 at 17:24

Hoan Nguyen

18,033
3
50
54

Android On Delete Cascade not working properly

2 Answers2