Delete nested rows from table with orphaned parent id

user_14087402 0
Viewed 219

I have one single table where the parentId column can be 0 for top level items or a number for items belonging to other items. There's no limit to how deep the nesting can go.

id     parentId     title
-------------------------
1      0            Item1
2      1            Item11
3      2            Item22
4      0            Item2
5      4            Item21

What I want is that every time I delete one item, every child and child of a child is deleted, until no item has a parentId of an item that does not exist.

I know I can loop through the records and query DELETE starting from the bottom child but I wonder if this can be done only with a SQLite command

delete-row android-sqlite sqlite android java
2 Answers

You can redefine the table:

CREATE TABLE tablename (
  `id` INTEGER PRIMARY KEY,
  `parentId` INTEGER,
  `title` VARCHAR(6),
  FOREIGN KEY (`parentId`) REFERENCES tablename(`id`) ON DELETE CASCADE
);

so that the column parentId is a foreign key referencing the column id which must be the PRIMARY KEY of the table or must have a UNIQUE index/constraint.

The ON DELETE CASCADE action makes sure that when you delete a row all the children rows will also be deleted (also the children of the children rows and so on).

Also instead of 0 you must set the top level items to null so there is no foreign key constraint violation.

You must turn on foreign key support because it is off by default and this can be done in SQLiteOpenHelper's onConfigure() method:

@Override
public void onConfigure(SQLiteDatabase db){
    db.setForeignKeyConstraintsEnabled(true);
}

Now when you delete a row from the table all the rows of the levels below the level of the row that you deleted will also be deleted.

You may have to uninstall the app from the device so that the database is deleted and rerun to recreate the database and the table with the new definition.

See a demo.

user_104988280

This code will delete every single row that has this "ID" in id column or parentId column:

void deleteItemAndChildren(String ID){
        //Deletes the row that has that ID in id column
        int deletedIdsCount = db.delete(tableName, "id=?", new String[]{ID});

        //Deletes the row that has that ID in parentId column
        int deletedChildrenCount = db.delete(tableName, "parentId=?", new String[]{ID});

        Log.i("CountOfDeletedRows", "was "+ ID +" Deleted: " + (deletedIdsCount>0) + " Count of children deleted: " + deletedChildrenCount );
}

Cheers!

user_100057520
Related