Re: relationship in the database

In article <am55eb$hac$1_at_sp15at20.hursley.ibm.com>, Paul Vernon <paul.vernon_at_ukk.ibmm.comm> wrote:
>>Yes, but the question I tried to answer was what the equivalent
>>version of ON UPDATE CASCEDE would look like in the ER model.
>
>BTW I don't agree that ON UPDATE CASCADE have a place in the RM model. Or
>at least the concept needs to be consistent with relational assignment.

I think Costin is right when he says that the ON UPDATE CASCADE can sometimes be very useful. However, I think the concept is consistent with relational asignment. For example, ON UPDATE CASCADE becomes a trigger that checks if after the assignment a certain candidate key value has disappeard and a new one has been added. If so then it performs an assignment to the referring relation that replaces the tuples that refer to the old cand. key value with tuples that refer to the new cand. key value.

>As INSERT/UPDATE/DELETEs are only macros for relational assignment
>operations. Having both an ON UPDATE CASCADE and an ON DELETE CASCADE on
>one FK means that an UPDATE of a CK and an otherwise equivalent
>DELETE/INSERT statement pair can result in different database values. The
>question then is, what value would result if the operation was the
>equivalent relational assignment, rather than the UPDATE or DELETE/INSERT?

That's an inherent problem with triggers; you have the same problem if you allow such triggers for relational assignments. This particular problem has to do with the question when the triggers are fired: after every assignment or after the transaction has ended. The first is easier to implement, the second is formally cleaner.

>Cascade actions that work differently depending on INSERT/UPDATE/DELETE
>can only properly be placed as part of the macro expansion of the I/U/Ds.
>Amongst other things this implies that the users should be able to see
>(and review) the cascaded actions that are being suggested on their
>behalf. It also implies that cascaded actions cannot be mandated, and so
>IMO they don't have a place in RM (or ER) model.

But in some sense you could say that since the user put those triggers there he or she thus mandated these updates. But I also agree that this is dangerous in the sense that they look very simple but it is quite hard to oversee all their consequences.

As far as the ER model is concerned, I also agree, but as I already showed you can do what Costin wanted by introducing a REPLACE operation which is quite natural in the ER context, so you don't need those ugly triggers. Which is yet another reason why the ER model is better than the Relational model. :-)

• Jan Hidders