Delete nested rows from table with orphaned parent id

Question

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

Answer 1

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.