[BUG] - Unable to Delete Food If It Appears On A Shopping List Item

[michael-genson]

First Check

• This is not a feature request.
• I added a very descriptive title to this issue (title field is above this).
• I used the GitHub search to find a similar issue and didn't find it.
• I searched the Mealie documentation, with the integrated search.
• I already read the docs and didn't find an answer.
• This issue can be replicated on the demo site (https://demo.mealie.io/).

What is the issue you are experiencing?

When trying to delete a food that is being used on a shopping list, an error is thrown. Peeking into the error message, I see:

{
  "detail": {
    "message": "An unexpected error occurred",
    "error": true,
    "exception": "(psycopg2.errors.ForeignKeyViolation) update or delete on table \"ingredient_foods\" violates foreign key constraint \"shopping_list_items_food_id_fkey\" on table \"shopping_list_items\"\nDETAIL:  Key (id)=(3927a452-da8b-4c24-bd2d-d463433b84c4) is still referenced from table \"shopping_list_items\".\n\n[SQL: DELETE FROM ingredient_foods WHERE ingredient_foods.id = %(id)s::UUID]\n[parameters: {'id': '3927a452-da8b-4c24-bd2d-d463433b84c4'}]\n(Background on this error at: https://sqlalche.me/e/20/gkpj)"
  }
}

Steps to Reproduce

1. Create a food
2. Add the food to a shopping list item
3. Attempt to delete the food (e.g. from the Data Management page)

Please provide relevant logs

See above

Mealie Version

Nightly

Deployment

Docker (Synology)

Additional Deployment Details

Using postgres. Couldn't reproduce this on nightly, so it might be DB specific

[boc-the-git]

Based on something I experienced in the unit tests for this PR, I think SQLite might not be great at enforcing foreign keys. Possibly it doesn't take notice of them at all.

I don't have a Postgres instance handy to actually 'confirm' this bug, but from just reading the title my immediate thought was "that's a foreign key constraint".

I am curious what the actual impact of SQLite not catching this issue is..

A solution might be to implement at the time of deletion a check on various places (e.g. shopping list) that an entity can be referenced. Feels like some overhead though. 🤔

[github-actions]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.