SQL and Relational Theory, 2nd Edition

UPDATE OPERATIONS

I claimed earlier that The Principle of Interchangeability implies that views must be updatable (i.e., assignable to). Now, I can hear some readers objecting right away: Surely some views just can’t be updated, can they? For example, consider a view defined as the join—a many to many join, observe—of relvars S and P on {CITY}; surely we can’t insert a tuple into, or delete a tuple from, that view, can we? Note: I apologize for the sloppy manner of speaking here; as we know from Chapter 5, there’s no such thing as “inserting or deleting a tuple” in the relational model. But to be too pedantic about such matters in the present discussion would get in the way of understanding, probably.

Well, even if it’s true—which it might or might not be—that we can’t insert a tuple into or delete a tuple from S JOIN P, let me point out that certain updates on certain base relvars can’t be done, either. For example, inserting a tuple into relvar SP will fail if the SNO value in that tuple doesn’t currently exist in relvar S. Thus, updates on base relvars can always fail on integrity constraint violations—and the same is true for updates on views. In other words, it isn’t that some views are inherently nonupdatable; rather, it’s that some updates on some views will fail on integrity constraint violations (i.e., violations of The Golden Rule). Note: Actually, updates, on both base relvars and views, can fail on violations of The Assignment Principle too, as we’ll quickly see.

To illustrate ...