The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

(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:)

asked 12 Dec '09, 22:07

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645
accept rate: 32%

Funny sidenote: This is the first time if was asked "Are you a human being?" when raising a question on SQLA. Too academic?

(12 Dec '09, 22:09) Volker Barth

Well, are you? ;-))

(14 Dec '09, 16:50) Reimer Pods

@Reimer: What do you mean - human or too academic? Well, I solved the CAPTCHA so consider me human - at least according to current Turing test definitions:)

(14 Dec '09, 17:22) Volker Barth

Sounds reasonable, even if I can't recall a situation where something like that would have been usefull. CHILD MANDATORY came to my mind as a tag for the parent, but that lead to this question: which side of the parent-to-child-relation should hold the constraint?

permanent link

answered 14 Dec '09, 17:19

Reimer%20Pods's gravatar image

Reimer Pods
4.1k324278
accept rate: 12%

And of course there would be a practical problem: How would one create a parent object without first creating (and linking) the child. "The hen and the egg"...

(14 Dec '09, 17:24) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×58
×22
×5
×4

question asked: 12 Dec '09, 22:07

question was seen: 2,035 times

last updated: 14 Dec '09, 17:19