2010-12-18

Cascade delete and Triggers

If you are using a database in your android application you are probably going to delete data at some point. When dealing with one to many relationships, the children should also be deleted when the master is deleted.

For example, a table Album contains many songs stored in another table Songs. The table Songs has a foreign key album_id referencing the primary key id in table Album.

Sqlite3 doesn't support cascade delete until version 3.6.19 (available on Android 2.2), so if you want this to work on a device running android prior to version 2.2 it can be solved using Triggers:

In your database helper class, create a String containg the trigger command:
private static final String CREATE_TRIGGER_DELETE_ALBUM =
     "CREATE TRIGGER fkd_songs_album_id "
       +"BEFORE DELETE ON Album "
       +"FOR EACH ROW BEGIN "
       +"DELETE FROM Songs WHERE album_id = OLD._id; " 
       +"END;";
and add this after you have created the tables:
@Override
  public void onCreate(SQLiteDatabase db) {
       //Create tables
        .
        .
        db.execSQL(CREATE_TRIGGER_DELETE_ALBUM);
  }
Now, when deleting an Album all of it's songs will also be deleted.

Inga kommentarer:

Skicka en kommentar