override constraint from no action to cascading at runtime

Question

I feel like I have a verry basic/stupid question, yet I never saw/read/heard anything in this direction.

Say I have a table users(userId, name) and a table preferences(id, userId, language). The example is trivial, but could be extended to a situation with multi-level relations and way more tables.. When my UI requests to delete a user I first want to show a warning stating that also its preferences will be deleted. If at some point the database gets extended with more tables and relationships, but the software isn't adapted accordingly (client didn't update) a generic message should be shown.

How can I implement this? The UI cannot know about the whole data structure and should not be bothered to walk down all the relations to manually delete all the depending records.
I would think this would be with constraints. The constraint would be no action at first so the constraint will throw an error that can be caught by the UI. After the UI receives a confirmation, the constraint should become a cascade.

Somehow I'm feeling like I'm getting this all wrong..

Answer 1

What I would do is this:

1. The constraint is CASCADE
2. The application checks if preferences exist.
3. If they do, show the warning.
4. If no preferences exist, or the warning is accepted, delete the client.

Changing database relationships on the fly is not going to be a good idea!!

Cheers,

RB.

Answer 2

Yes, this is what I would do. But it requires that you know all the constraints on beforehand. What if at some point I added a field moderates to my user table and a table Forums(forumId, description) that it refers too. The user is still using the old client (he didn't update instantly) but that shouldn't matter because the field is added. He won't be able to make a user a moderator, but he also is not able to delete users that are moderators anymore. Please don't mind the question if the client should be able to delete the user without knowing all the consequences for this is just an example.

Answer 3

If you are worried about the user not realising the full impact of their delete, you might want to consider not actually deleting the data - instead you could simply set a flag on a column called say "marked_for_deletion". (the entries could then be deleted a safe time later)
The downside is that you need to remember to filter out the marked rows in other queries. This can be mitigated by creating a view on the table with the marked rows filtered out, and then always using the view in your queries.

Answer 4

Myes, this is a possibility, but the core question remains.

Maybe I should refrase the whole thing.

If I have a cascading constraint AND the row has depending rows I want to show a confirmation dialog. The checking of the cascading and the cascading itself should be done by the db server and NOT by the client.