How should I remove linked rows in SQLite with Android?

Question

I have a very simple link table set up, I need to delete rows from all 3 tables

Table 1 - Assignment {assignment_id}

LinkTable - AssignmentTasks {assignment_id, task_id}

Table 2 - Tasks {task_id}

I can delete from Assignment and AssignmentTasks easily as I have the Id but I don't have the list of Tasks related to this assignment.

I've built a cursor which returns all the task_ids related to an assignment, but I can't remove them whilst the records in the link table refer to them. (I don't think I can as the foreign key constraint should stop me deleting rows referenced elsewhere)

Do I need to store a list of task_ids, delete the assignment_tasks records, delete the assignment record then iterate through the stored list of task_ids and delete each task ? or is there a better way of doing this ?

score 1 · Accepted Answer · answered Mar 05 '12 at 13:12

Or you can turn foreign key constraint checking off temporarily:

pragma foreign_keys = off;

But that shouldn't be necessary.

The other issue here is that this is currently written as a many-to-many relationship. This would imply that multiple assignments could refer to a one task. It probably wouldn't be OK to delete the task just because one of the assignments referencing it was deleted. Instead, you would need to check that the task is no longer referenced before deleting it.

Alternatively, if you really meant for each task to belong to only one assignment, you could set your schema up like this:

CREATE TABLE Assignment (assignment_id int PRIMARY KEY);
CREATE TABLE Task (
    task_id int PRIMARY KEY,
    assignment_id int FOREIGN KEY REFERENCES Assignment ON DELETE CASCADE
);

The ON DELETE CASCADE bit causes the Task entry to be deleted in the event that the assignment it refers to is deleted. This only works if foreign key constraints are enabled, of course. If the assignment is being deleted by a trigger or due to some other cascade, you may need to enable recursive triggers as well with pragma recursive_triggers = on.

Another possibility (if you want to retain the original schema) is to make the foreign key references in the AssignmentTask table do the cascading delete. That way those rows are deleted automatically as you delete the Tasks. Then you can delete the Assignment once all of those are taken care of.

I've not got a many to many, I just cut out the table details for brevity. I'll look into 'ON DELETE CASCADE' — Luke, Mar 05 '12 at 13:30

score 1 · Answer 2 · edited May 23 '17 at 12:12

Why not query the task_id based upon the assignment_id that you intent to delete.

You can leave the deletion of the related data to the database but that depends if you have defined cascade action onDelete when you created the relationships.

If you intend to use cascade onDelete as far as I know you need to enable that on sqlite. See this post for how to. Foreign key constraints in Android using SQLite? on Delete cascade

You can use a raw query. e.g.

delete from tasks where task_id in (select task_id from assignments where assignment_id=your_assignment_id_here)

I would also suggest using transaction for this for two reason a) so you can rollback in case the either of your queries fail. b) sqlite in general works better with transactions speedwise if you have multiple queries. So place all of your delete queires for a given action in a transaction.

Link below is for rawQuery: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#rawQuery(java.lang.String, java.lang.String[])

Thanks, looks like both you guys reccomended enabling 'ON DELETE CASCADE' I'll also look into implementing transactions in an attempt to better deal with the case where one or more of the queries fails. — Luke, Mar 05 '12 at 13:32

How should I remove linked rows in SQLite with Android?

2 Answers2