Here is an example of how to set up a Cascade Delete:
First, let's show an example database with a parent table (author) and a child table (book).
First, we need to build a Custom Function that deletes a record from the child table. This Function would look exactly like the Delete API endpoint for the child table.
First, create an input that is a table reference to the child table.
Second, add a delete record function for the child table function. (Function Stack > Database Requests > Delete Record).
Next, map the field_value in the delete record function to the input.
Next, we need to create another Custom Function (or Custom API endpoint depending on how you'd like the cascade delete to be executed) that deletes a record from the parent table and performs the cascade delete.
First, create an input that is a table reference to the parent table.
Second, add Query All Records function for the child table. (Function Stack > Database Requests > Query All Records).
Next, click on the Query All Records function and select Custom Query on the Filter tab.
Once we click on Custom Query, the Expression Builder will open up.
In the Expression Builder, we must define the parameters to have the foreign key in the child table equal to the input of the parent table id. (In other words, the foreign key is the table reference or relationship to the parent table in the child table).
Next, we need to add a For Loop function to the Function Stack. (Function Stack > Data Manipulation > For Loop).
Make sure the For Loop is mapped to the variable of the Query All Records function.
Then, we will add to "Define Functions" of the For Loop by clicking the + button.
Next, add the Custom Function we created that deletes a record from the child table. (+ > Custom Functions).
Next, click on the Function that deletes the record from the child table within the For Loop.
Map the input to the variable name of the For Loop. Then, use dot notation to drill down to the id.
The final step is to add a Function to the Function Stack that deletes a record in the parent table. (Function Stack > Database Requests > Delete Record).
Then, map the inputs so the field_value equals the input of the parent table id.
Finally, the cascade delete is ready!
Here is a video showing you how to perform a cascade delete for further reference: