Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: delete cascade

Re: delete cascade

From: paul c <>
Date: Mon, 23 Apr 2007 21:43:44 GMT
Message-ID: <kM9Xh.118787$DE1.98245@pd7urf2no>

Brian Selzer wrote:
> "paul c" <> wrote in message
> news:y9PWh.115941$DE1.92555_at_pd7urf2no...

>>Brian Selzer wrote:
>>>"paul c" <> wrote in message
>>>>Eg., the rva has an attribute that references the invoice# in the same 
>>>>relation that has the rva as an attribute.
>>>Including invoice# in the header of the rva would be redundant because 
>>>for each element of the relation, every tuple in the value for the rva 
>>>would have the same invoice# as the element containing the value.  ...
>>Well, if the view had been formed by a join of invoices and items and the 
>>only common attribute was invoice# and then some of the other attributes 
>>were grouped, invoice# would appear in only one of the headers.
>>That is different from saying invoice# in the rva is always redundant 
>>which would preclude a relation with invoice# as an attribute of both 
>>headers, for example, the tuple {invoice# x, rv {item#, invoice#}} where 
>>rv is empty.  I'm not quite sure what use such a relation would have, but 
>>I don't see why one should take steps to prevent it if we can express it.

> Actually, the tuple {invoice# x, rv {item#}} would work just as well, unless
> the invoice# in the rv could possibly refer to some other invoice???
> ...

Of course it could and that might be the most obvious use. However, while people can read anything they want into a predicate as long as they agree on the "anything" and as long as a dbms doesn't do something to distort that, when discussing dbms behaviour, one must read relation definitions precisely. The power of a dbms lies much in its ability to obey a formalization. In a sense, the dbms respects as well as manipulates relation predicates without knowing what humans think they are! As yet, it is not clear to me that anything in say, normalization theory, deals with this situation, nor do operators such as Group/Ungroup hold any promise as it is known that they aren't always reversible. Until somebody gets their arms around this, I suggest it's better to not jump to conclusions about what the reference would need to be.


>>(By my definition, regardless of whether rv were empty, rv.invoice# could 
>>reference the "first" invoice# attribute.)
>>>... Since a tuple is a value, not a variable, the rva containing the 
>>>items would be assigned at the same instant as the containing tuple. 
>>>This eliminates the need for a referential action.  The thing I don't 
>>>understand is why Bob suggested using a view.  If composition rather than 
>>>aggregation applies, then make the rva part of a base relation.  A view 
>>>would add unnecessary complication to the schema, leaving room for human 
>>>error.  I also am mystified that he mentioned cascading updates: from 
>>>what he has said in earlier posts, keys can't be updated, and if keys 
>>>can't be updated, there's no need for cascading updates.
>>On a logical level, I don't know what a "referential action" is.  I am 
>>supposing it means the same as "reference". (I'm not sure I've ever 
>>understood why the foreign key kind of reference is not called implication 
>>as its definition seems to take the same form as logical implication.)

> I know it sounds backwards, but a foreign key constraint describes a
> whole-part relationship, with the referencing, or "child," relation taking
> the role of the whole and the referenced, or "parent," relation taking the
> role of the part. All of the attributes values from a referenced tuple can
> be thought of as being an integral part of the referencing tuple.
> Therefore, any update to an attribute of a part should automatically be
> reflected in the whole, regardless of whether the attribute is prime. By
> its participation in a foreign key constraint, a key takes on its own
> identity within the database, meaning that whenever that key is updated,
> that update must also be reflected in every referencing tuple. A cascading
> update relies on the concept of logical identity in that there is
> theoretically only one value for a key, regardless of the fact that that key
> value may appear not only in the referenced tuple, but in numerous other
> referencing tuples throughout the database. For cascading deletes, on the
> other hand, the roles are reversed. The only time that a cascading delete
> should be allowed is when the entities represented by each referencing tuple
> are not only a part of the entitiy represented by the referenced tuple, but
> also are controlled with respect to lifetime by the entity represented by
> the referenced tuple. The line items of an invoice fall into this category.
> That's why you can use a relation valued attribute for line items in the
> schema for an invoice.

That is a lot of verbiage compared to "(A{C} <AND> B{C}) = A{C}" or


F  F | T
F  T | T
T  F | F
T  T | T

It makes me wonder if mysticism isn't creeping up again.

As for UPDATE, it may be that I do not appreciate it fully, still not being able to see a fundamental place for it in the RT, let alone a formal definition. It may be a language or environment feature and those may involve dbms's, but such a connection is not by itself enough for me to make the transposition.

p Received on Mon Apr 23 2007 - 16:43:44 CDT

Original text of this message