How to delete related sub tables when item is deleted?

Wappler General Need Help
1

I’m trying to set up a database and the related actions but am not sure how to work something. My basic layout is

User
(user sub table) Collection
(collection sub table) Item

Basically, users can make different collections and each collection can hold items within it.

How can I make it so that when a user deletes a collection it will delete all the items in that collection?

Similarly, if a user deletes their account, I want to delete all of their collections and all of the items in their collections.

I’m not sure how cascade on delete works and if it would do what I need or if I have to do something like fetch the id of all the items in the collection and then use a database delete on those items when I delete the collection.

I’m assuming there is an easy solution I’m just not sure the right way to do it. Thanks for any help.

2

Sounds like you already created a relation the CASCADE on delete means lets say you have 5 items which all have a collection_id of 5

when you delete the collection with id 5 any items referencing the 5 will be deleted

e.g

collection table
collection_id | name
1 | collection one
2 | collection two
3 | collection three

item table
item_id | collection_id | something
1 | 3 | random
2 | 3 | values
3 | 1 | here

If you was to delete:
3 | collection three

These two items would be deleted as they have the collection_id:
1 | 3 | random
2 | 3 | values

3 Likes
3

I thought that’s how it worked but I guess I’m doing something wrong.

I made a collection

1610×51

and two items(called links here)that are connected to the collection

1291×82

all the on delete for them is set to cascade

357×981

then I use an action to delete a collection

and it deletes the collection

1608×105

but not the links(items)

1289×115

any idea what I’m doing wrong?

4

Are you using SQLite by any chance?

5

Yes

6

I have not been able to make the cascade work in SQLite.

What I do is to add an extra step to delete the Links that are related to the deleted Collection.

As a side note: I am sure that this is not a failure of SQLite, meaning that there may be a bug in Wappler.

1 Like
7

Well it seems that per default SQLite has its foreign keys off per default, so referential integrity is not enforced.

https://www.sqlite.org/foreignkeys.html

Maybe we should probably enable it automatically, maybe post a separate bug report about this:

2 Likes
8

Get a few brownie points.

9

If anyone is still encountering this issue, a simple solution is to add a Database Custom Query before performing an update or delete operation. Use the following line:

PRAGMA foreign_keys = ON;