[odb-users] Best Practice deleting persistent objects

[Steven Côté]

I've been finding my way using odb lately and I've run into a situation
that I'm not quite sure how to handle. At least not "correctly".

I have a persistent class representing a "user" and another persistent
class representing a "user group". The user group is basically just a name
and a list of "user" objects.

Running through the schema generator, this gives me three tables; user,
group and group_member. The third table just contains the mapping between
users and groups.

All good so far.

The question came when I tried to delete a user that was a member of a
group. This threw a "FOREIGN KEY constraint failed" exception because now
there was an entry in the group_member table to a user that didn't exist.

So, my question is how is this best handled using odb?

Obviously, we have to remove all references to the user about to be deleted
from the group_member table. My first thought was to explicitly remove
references to the user from the mapping table using erase_query(), but
since the mapping table isn't one of my "persistent classes", that didn't
seem right. Using just raw SQL also seems a bit heavy handed.

Traditionally, this is the sort of thing I'd just handle with ON DELETE
CASCADE in the schema, but since odb doesn't put those in, I wanted to see
if there's another way.

Any thoughts/suggestions?