Databases Reference
In-Depth Information
BASE TABLES ONLY: COMPENSATORY ACTIONS
Now, in order to ensure that the constraints outlined in the previous section continue to hold
when certain updates are done, certain
compensatory actions
need to be in effect. In general, a
compensatory action—also known as a
compensating
action—is an additional update (over and
above some update explicitly requested by the user) that's performed automatically by the
DBMS, precisely in order to avoid some integrity violation that might otherwise occur.
5
Cascade
delete is a typical example.
6
In the case at hand, in fact, it should be clear that cascading is
exactly what we need to deal with DELETE operations in particular. To be specific, deleting
rows from either LS or NLS clearly needs to cascade to cause those same rows to be deleted
from S. So we might imagine a couple of compensatory actions—actually cascade delete
rules—that look something like this (hypothetical syntax):
ON DELETE
d
FROM LS : DELETE
d
FROM S ;
ON DELETE
d
FROM NLS : DELETE
d
FROM S ;
Likewise, deleting rows from S clearly needs to cascade to cause those same rows to be
deleted from whichever of LS or NLS they appear in:
ON DELETE
d
FROM S : DELETE (
d
WHERE CITY = 'London' ) FROM LS ,
DELETE (
d
WHERE CITY <> 'London' ) FROM NLS ;
As an aside, I remark that, given that an attempt to delete a nonexistent row has no effect—or so
I'm going to assume, at any rate—we could replace each of the expressions in parentheses in the
foregoing rule by just
d
. However, the expressions in parentheses are perhaps preferable, at least
inasmuch as they're clearly more specific.
Analogously, we'll need some compensatory actions (“cascade insert rules”) for INSERT
operations:
ON INSERT
i
INTO LS : INSERT
i
INTO S ;
ON INSERT
i
INTO NLS : INSERT
i
INTO S ;
ON INSERT
i
INTO S : INSERT (
i
WHERE CITY = 'London' ) INTO LS ,
INSERT (
i
WHERE CITY <> 'London' ) INTO NLS ;
5
One reviewer asked why I chose the term
compensatory action
for this construct. Well, I should have thought the answer was
obvious, but in case it isn't, let me spell it out: The reason I call such actions “compensatory” is because they cause a second
update to be done to compensate for the effects of the first (speaking a trifle loosely, of course).
6
Cascade delete is usually thought of as applying to foreign key constraints specifically; however, the concept of compensatory
actions is actually more general and applies to constraints of many kinds.
