Databases Reference
In-Depth Information
Now what happens is this:
1. The existing tuple for supplier S2 is deleted from relvar NLS.
2. An attempt is made to insert a new tuple for supplier S2, with CITY value London, into
relvar NLS. That attempt fails, however, because it violates the constraint on relvar NLS
that the CITY value in that relvar can never be London. So the update fails overall; the
previous step (viz., deleting the original tple for supplier S2 from NLS) is undone, and the
net effect is that the database remains unchanged.
SUPPLIERS AND SHIPMENTS
Note: This section is something of a digression and might well be skipped on a first reading.
For one thing, the example it's based on is different in kind from the one I've been considering in
this chapter so far (i.e., London vs. non London suppliers)—it has to do with relvars S and SP
from the suppliers-and-parts database, and of course there's no question of one of those relvars
being a restriction of the other, and certainly not of one being a view of the other. For another,
the matters to be discussed, though they do have to do with updating in general, don't seem to
have much to do with view updating (or redundancy control) in particular. For such reasons,
the discussion that follows doesn't really belong in this chapter at all; rather, it ought to have
been included in either Chapter 2 or Chapter 3. But it does rely on certain material—especially
the material on explicit UPDATE operations from the section immediately preceding—that
wasn't covered (and couldn't sensibly have been covered) in either Chapter 2 or Chapter 3;
hence my decision to include it here.
Consider the foreign key constraint on the suppliers-and-parts database from the shipments relvar
SP to the suppliers relvar S. Assume for the sake of discussion that we want to specify a cascade
delete rule in connection with that foreign key, thus:
11
ON DELETE
d
FROM S : DELETE ( SP MATCHING
d
) FROM SP ;
11
In SQL, such a rule would be more simply (?) defined by means of a specification of the form ON DELETE CASCADE,
included, a trifle illogically, as part of the CREATE TABLE statement for table SP. (I say “illogically” because the delete that's
referred to in that specification is a delete on table S, not table SP.) I prefer a definition that (a) uses my own deliberately verbose
syntax and (b) stands alone as a separate statement in its own right.
