Sqlite replace and foreign keys in Android

Question

I have the following table structure:

Table Days:          [_id,date,name]
                       ^
                       |
Table Events: [_id, day_id ,description]

The foreign key day_id is set to ON DELETE CASCADE

I want to "insert or update" the day 2. So I make:

ContentValues values= new ContentValues();
values.put("_id",2);
values.put("date,"...");
values.put("name","welcome");
mDb.replace("days",null,values);

The problem is that if the day_id=2 already exists (in my case is the most probably option) the DBengine deletes the table and inserts a new row so all the events associated to that day are also deleted.

This solution works but its very sub-optimal right?

    try {
        mDb.insertOrThrow("days", null, values);
    } catch (Exception e) {
        mDb.update("days", values, "_id=2", null);
    }

Whats the correct sollution for that problem

score 1 · Accepted Answer · answered Apr 16 '12 at 12:35

1

You should add your reference as below in order to allow SQLite to delete or update the referenced column automatically.

FOREIGN KEY [_id] REFERENCES [days] ON DELETE CASCADE ON UPDATE CASCADE

answered Apr 16 '12 at 12:35

waqaslam

67,549
16
165
178

Thanks for your answer. I don't want to update the day _id, just "insert or update" it but that instruction doesn't exists in sqlite right? (using replace the events associated to the days are deleted on cascade when the old row is deleted before inserting the "updated one") – Addev Apr 16 '12 at 12:55
if you update a primary key, **ON UPDATE CASCADE** will automatically reflect the changes in its referenced columns, so you dont need to worry about deleting and then inserting it – waqaslam Apr 16 '12 at 13:00
1

but the replace command makes a (delete + insert) automatically if the column already exists =( – Addev Apr 16 '12 at 13:44
what do you mean *replace command*? aren't you only relying on foreign key cascades? – waqaslam Apr 16 '12 at 14:17
I don't think that this answer answers the question. – nickgrim Oct 16 '15 at 10:24
This doesn't work for me. When I `INSERT OR REPLACE` into one table, the referenced row from another table gets deleted :( – ntoskrnl Jul 13 '17 at 09:22
1

this answer shouldn't be marked as accepted as it doesn't solve the OP's problem. I found another thing that one might try to address the issue: https://stackoverflow.com/a/32554601/1199452 – use delete trigger. – ntoskrnl Jul 13 '17 at 09:30

Sqlite replace and foreign keys in Android

1 Answers1