英文:
Delete nested rows from table with orphaned parent id
问题
我有一张单一的表，其中 parentId 列可以是 0，表示顶级项目，或者是一个数字，表示属于其他项目的项目。嵌套的层级没有限制。
id parentId title
-------------------------
1 0 Item1
2 1 Item11
3 2 Item22
4 0 Item2
5 4 Item21
我想要的是，每当我删除一个项目时，所有子项目和子项目的子项目都会被删除，直到没有任何项目的 parentId 是不存在的项目。
我知道我可以遍历记录，并从底层子项目开始查询 DELETE，但我想知道是否可以仅通过 SQLite 命令来实现这一点。
英文:
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
答案1
得分: 1
你可以重新定义表格：
CREATE TABLE tablename (
`id` INTEGER PRIMARY KEY,
`parentId` INTEGER,
`title` VARCHAR(6),
FOREIGN KEY (`parentId`) REFERENCES tablename(`id`) ON DELETE CASCADE
);
这样，列 parentId 将作为一个外键，引用了列 id，而这列 id 必须是表格的主键，或者必须有一个 UNIQUE 索引/约束。
ON DELETE CASCADE 操作确保当你删除一行时，所有子行也将被删除（以及子行的子行，以此类推）。
同时，你必须将顶层项目的值从 0 改为 null，这样才不会违反外键约束。
你必须打开外键支持，因为默认情况下是关闭的，这可以在 SQLiteOpenHelper 的 onConfigure() 方法中完成：
@Override
public void onConfigure(SQLiteDatabase db){
db.setForeignKeyConstraintsEnabled(true);
}
现在，当你从表格中删除一行时，该行下层级的所有行也将被删除。
你可能需要从设备中卸载应用，以便删除数据库，并重新运行以重新创建具有新定义的数据库和表格。
查看演示：demo。
英文:
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.<br/>
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).<br/>
Also instead of 0 you must set the top level items to null so there is no foreign key constraint violation.<br/>
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.<br/>
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.<br/>
See a demo.
答案2
得分: 0
这段代码将会删除所有在 id 列或者 parentId 列中具有这个“ID”的单独行：
void deleteItemAndChildren(String ID){
// 删除具有该ID的行（位于id列中）
int deletedIdsCount = db.delete(tableName, "id=?", new String[]{ID});
// 删除具有该ID的行（位于parentId列中）
int deletedChildrenCount = db.delete(tableName, "parentId=?", new String[]{ID});
Log.i("CountOfDeletedRows", "被删除：" + ID + " 行数：" + (deletedIdsCount>0) + " 被删除的子行数：" + deletedChildrenCount );
}
祝好！
英文:
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!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论