Databases Reference
In-Depth Information
Note:
The concept of cascade insert doesn't usually arise in connection with foreign key
constraints, of course, but that's no reason not to support such a concept in general. More
important, don't get the idea that compensatory actions must always take the form of simple
cascades. While the ones discussed in this introductory chapter do all happen to take that form,
more complicated cases are likely to require actions of some less straightforward form, as we'll
see in later chapters.
As for UPDATE operations, they can be regarded, at least in the case at hand, as a
DELETE and an INSERT taken in combination; as a consequence, the necessary compensatory
actions are just a combination of the corresponding delete and insert actions, loosely speaking.
For example, consider the following UPDATE on table S:
UPDATE S
SET CITY = 'Oslo'
WHERE SNO = 'S1' ;
What happens here is this:
1. The existing row for supplier S1 is deleted from table S and a new row for that supplier,
with CITY value Oslo, is inserted into that same table.
2. The existing row for supplier S1 is deleted from table LS as well, thanks to the cascade
delete rule from S to LS, and the new row for that supplier, with CITY value Oslo, is
inserted into table NLS as well, thanks to the cascade insert rule from S to NLS. In other
words, the row for supplier S1 has “migrated” from table LS to table NLS! (Of course,
here I'm speaking very loosely indeed.)
Suppose now that the original UPDATE had been directed at table LS rather than table S:
UPDATE LS
SET CITY = 'Oslo'
WHERE SNO = 'S1' ;
Now what happens is this:
1. The existing row for supplier S1 is deleted from table LS.
2. An attempt is made to insert a new row for supplier S1, with CITY value Oslo, into table
LS. That attempt fails, however, because it violates the constraint on table LS that the
CITY value in that table must always be London. So the update fails overall; the previous
step (viz., deleting the original row for supplier S1 from LS) is undone, and the net effect is
that the database remains unchanged.
