foreign key constraint ON DELETE CASCADE not working in sqlite database on android

Question

I have "days" table created as follows

"create table days(" +
            "day_id  integer primary key autoincrement, " +
            "conference_id integer , " +
            "day_date text, " +
            "day_start_time text, " +
            "day_end_time text, " +
            "day_summary text, " +
            "day_description text)";

and i have tracks table created as follows

CREATE_TABLE_TRACK = "create table track(" +
        "track_id integer primary key autoincrement," +
        "day_id integer,"+
        "track_name text," +
        "track_description text," +
        " FOREIGN KEY(day_id) REFERENCES days(day_id) ON DELETE CASCADE )";

as shown above i have foreign key day_id referencing to the day_id of table days...

So what i want is if i delete the day then corresponding track should also be deleted... But it does't happen in my case..

I have sqlite with version 3.5.9

And also i have added 1 line in my helper class as

> db.execSQL("PRAGMA foreign_keys=ON;");

but is still won't work.. please help me out..

Huberman

but as i said i have added the line "PRAGMA foreign_keys=ON;" in my sqlite helper class – Huberman

Answer 1

Cascading delete isn't supported until Sqlite version 3.6.19, which is first included on Android 2.2.

Fortunately there is an alternative.

You can execute another query like this below your create table query:

db.execSQL("CREATE TRIGGER delete_days_with track BEFORE DELETE ON track "
       +  "FOR EACH ROW BEGIN"
       +         " DELETE FROM days WHERE track.day_id = days.day_id "
       +  "END;");

Note that delete_days_with_track is just a name descriptive of what the trigger does, and this is just the pattern I use; I believe you could name it anything you wish.

Matelote

This is not working for me. I've to use (WHERE OLD.day_id = days.day_id) to get it working. I've spent a lot of time until I released it. For more info. sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers – Farflung

Funny. I have 3.7.9 here on an Ubuntu 12.04 machine and ON DELETE CASCADE doesn't work either. Strangely, neither does the trigger: translated to the given example, it claims "Error: no such column: track.day_id" – though a SELECT * FROM track JOIN days ON track.day_id=days.day_id works fine. I'm confused. – Daliadalila

Answer 2

According to the SQLite Documentation support for Foreign Keys was not added until 3.6.19.

Using 3.5.9 you'll have to do your cascade deletions in some other manner.

Cress

So is there any way to upgrade sqlite version or any other way?? because i have to support my app on android version 2.1 onwards – Huberman

You do not have to do deletes manually, see the trigger query below. – Matelote

@jkschneider, Ah, thanks! I use SQLite, but I make no claim to being an expert. Thanks for teaching me something new. – Cress

@Rashmi, see jkschneider's answer for a solution – Cress

@Barak, I wish I could say I was an expert, but I wound up doing manual deletes for several months before stumbling across that trick somewhere... Oh the joy of having to go clean up code. – Matelote

@jkschneider, well, misery loves company and I just joined you with that bit of information... at least three apps and MANY data chains I can get rid of. Thanks again :) – Cress