Consider CASCADE delete for Question and Answer when deleting a Questionnaire

[Meefish]

Error Message:

answerService.ts:63 Error deleting answer: Error: Failed to delete answer: {"$schema":"https://wildlifenl-uu-michi011.apps.cl01.cp.its.uu.nl/schemas/ErrorModel.json","title":"Bad Request","status":400,"detail":"(ID)=(03785751-db5f-4242-a77f-490b70f59e35) is still used by 'message'."}

at deleteAnswer (answerService.ts:60:1)
at async saveQuestionsAndAnswers (QuestionEdit.tsx:210:1)

[BasMichielsen]

Technically any 4xx error is a client mistake. You should delete the Message first and then this Answer. I realize this is "uncomfortable" but it is not a bug, and the correct working of the API back-end is still guaranteed. I do think it is worthwhile to investigate a possibility for making this less uncomfortable on the client side. So let's make this issue an enhancement.

[BasMichielsen]

This issue arises in all DELETE calls for Answer, Question, Questionnaire and Experiment when attempting to delete them from the system and they are not "empty". Where empty means that there is still a child object connected to this object, in other words, the relational DB still has a record in another table having a foreign key that points to the primary key of this record.

It is possible to resolve the "uncomfortable" aspect of this issue by changing the ON DELETE part of the table to CASCADE instead of RESTRICT. However, for the sake of prudence we have decided to NOT do this yet but may do so in the future.