Finding and deleting records where reference no longer exists

As your application grows, it's common to have related data stored across multiple tables. However, there might be situations where you need to remove orphaned records from a child table when their parent record no longer exists. In this guide, we'll walk you through the process of finding and deleting those orphaned records using Xano's no-code tools.

Step 1: Create a New API

First, we need to create a new API in Xano that will handle the deletion of orphaned records. Navigate to the API Builder and click on the "Create API" button. Give your API a descriptive name, such as "DeleteOrphanedDeals," and select the HTTP verb "DELETE" since we'll be deleting data.

Step 2: Query All Records from the Child Table

In the API Builder, add a new step to query all records from the child table where the orphaned records reside. In our example, we'll query all records from the "deal" table.

Step 3: Add an Existence Add-on

Next, we need to check if the parent record still exists for each child record. To achieve this, add an "Existence" add-on to the query step. This add-on will check if a related record exists in the parent table.

1. Click the "Add Add-on" button in the query step.
2. Select the "Existence" add-on from the list.
3. Map the add-on to the parent table's ID field (e.g., "merchantId").
4. Give the add-on a descriptive name (e.g., "_merchant").

Step 4: Iterate Through the Records

After querying all records and checking for the existence of the parent record, we need to iterate through the results. Add a new "For Each" step and select the variable containing the queried records (e.g., "allDeals").

Step 5: Delete Orphaned Records

Within the "For Each" loop, add a conditional step to check if the existence add-on is false for the current record. If it's false, it means the parent record no longer exists, and we can delete the child record.

1. Add a new "Conditional" step inside the "For Each" loop.
2. Set the condition to check if the existence add-on is false (e.g., `item._merchant == false`).
3. Inside the "True" branch of the conditional, add a new "Delete Records" step.
4. Configure the "Delete Records" step to delete from the child table (e.g., "deal") where the ID matches the current item's ID (e.g., `item.id`).

Step 6: Test and Deploy

After completing the steps above, your API should be able to find and delete orphaned records from the child table. Test the API by running it, and then deploy it to your production environment.

By following this guide, you've learned how to leverage Xano's no-code capabilities to maintain data integrity and keep your application's database clean and organized. Remember, Xano simplifies backend development, making it accessible to both non-technical users and experienced developers alike.