stac_fastapi.sqlalchemy: Delete collection API not working

[keul]

Delete API seems to not work, or at least the user receive an error message which lead to wrong assumptions.

1. Wrong error message

Example:

curl -X 'DELETE' \
  'http://localhost:8000/collections/my-collection-id' \
  -H 'accept: application/json'

What I got back is:

{
  "code": "ForeignKeyError",
  "description": "collection does not exist"
}

But the collection is there, the error seems related to a general error handling at https://github.com/stac-utils/stac-fastapi/blob/d8e3edad3254dd59945de755a2f212603f7fcb29/stac_fastapi/sqlalchemy/stac_fastapi/sqlalchemy/session.py#L30:L32

2. Why my delete is failing?

https://github.com/stac-utils/stac-fastapi/blob/d8e3edad3254dd59945de755a2f212603f7fcb29/stac_fastapi/sqlalchemy/stac_fastapi/sqlalchemy/transactions.py#L155:L158

I think this query is just trying to delete the collection record, while not deleting the items, so it triggers an integrity error (missing cascade).