In my database I have a
Task table. This table has a number of fields that are foreign keys to other tables. There are also a few link tables, that have
Task on one end.
When a new task is created (or an old task is updated) I need to make sure that these foreign keys exists, and return a meaningful error if they do not. This is important because the call originates from a web api, so consumers really need to know why their call did not work, so a detailed error message (without giving out too much) is crucial.
Since there are a dozen of foreign keys (and also some other conditions, like current user application account being active) that make task correct, instead of trying to insert and failing I'm trying to validate these before I insert.
Of course when I do this I need to make sure that when I actually insert the record these validations still hold true.
So let's assume a task references a group. From the web api perspective group is a reference data, and it returns a list of valid group keys in a prior call. It receives one of them back when it creates or updates a task as a
GroupId field on the
To make sure that this (and other) foreign keys are present in the database I open a
REPEATABLE READ transaction and check them one by one for existence before I attempt to insert the task. I then compile a list of those that are absent (if any) and I roll back the transaction and return these back so that the API can form a meaningful error message.