(Beware, that might be one of the first academical questions on SQLA.)
In a longer comment dialogue between Breck and me on the following question, I raised the following question - now asking it here:
Is there a DRI action that is directed from the child to the parent table?
I.e. the action would be initiated if a row in a child table would be modified or deleted, and the referenced row in the parent table would be altered - exactly the other way around as in a typical DRI action (like ON DELETE CASCADE).
I know the answer is "No!" for SQL Anywhere (and I guess, for other DBMSs, too), but I would like to ask if something like that has ever been put into thought. Breck has invented the following syntax (making its future quite improbable):
ON DELETE ANNIHILATE FAMILY
Why would I want such a feature (not with that name, for sure!)?
Well, at times when doing data-modelling, I feel the need to introduce a reference between a so-called child table and a parent table where the parent table would be a weak entity in ER terms whereas the child table would be a strong entity. One might call the weak entity a "common attribute" of the strong entity.
Say, I want to model a system with persons and their addresses. Several persons (typically family members) would have the same address, and therefore it would be handy to put the addresses in a separate table (say A) and have a reference in the persons table (say P) to that one (say there's a column *P.fk_A* declared as FK to A).
If the whole family relocates, one might just have to modify the one
according address entry.
If the family splits up (children leave their homes and the like), each
family member might get a reference to a different address.
And now, if the last reference to an address is revoked because of further
relocations or whatever, there would be no more need to store the
according address - after all, we are managing persons with their addresses
but not addresses alone.
In that scenario, it would be handy if one could express that the "parent-side" of a relationship would be influenced when no more child does reference that parent.
Today, I would use trigger code to delete the address if there's no more usage, and this feature might be uncommon enough to leave it that way. On the other hand, DRI actions have been introduced to prevent the usage of the more error-prone triggers for common tasks.
Other usages might be "code tables" where one might want to delete an entry when it isn't used anymore, or the tags used in SQLA: It seems that they get deleted (over night in some clean-up, methinks) when there is no more question tagged with them.
Any hints are highly appreciated:)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.