androidsqliteandroid-sqliteandroid-databaseandroid-query

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


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();
}

Solution

  • 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).