What is the best way to delete a record of multi tables?

Question

I have a database that has three tables and there is a class_id column in all three tables. i want to when i deleting a class_id , Deleted all records in the tables that have the class_id

I used a way , but I'm not sure this way is standard or no ?

tip : class_id in a table is primary key and in another tables are foreign key

    public void DeleteClass(int classId)
{
    String query = "class_id = ?";


    OpenDatabase();

    database.delete(tblName_Class, query , new String[]{String.valueOf(classId)});
    database.delete(tblName_Student, query , new String[]{String.valueOf(classId)});
    database.delete(tblName_StudentPerformance , query , new String[]{String.valueOf(classId)});

    close();

    Toast.makeText(context, "deleted !", Toast.LENGTH_SHORT).show();
}

score 2 · Accepted Answer · answered Mar 29 '18 at 15:02

2

It can depend on the structure of your database. If you have defined the foreign keys with an ON DELETE CASCADE, you would only need to delete from the Class table and it would automatically delete from the other two.

Now if you haven't defined the ON CASCADE DELETE, the way to do it is to delete first from the tables that have the foreign key (In your case Student and StudentPerformance) and then the one with the primary key (Class).

answered Mar 29 '18 at 15:02

cte6

637
4
8

1

i dont used ON DELETE CASCAD , How can I use this way ? my database is sqlite – Mar 29 '18 at 15:15
Do you mean how you use ON DELETE CASCADE? If so here is a link of a good explanation with examples: https://www.techonthenet.com/sqlite/foreign_keys/foreign_delete.php – cte6 Mar 29 '18 at 15:35

What is the best way to delete a record of multi tables?

1 Answers1