The first point I want to stress is that, regardless of what syntax we use to express it, relational assignment is a set level operation. (In fact, all operations in the relational model are set level, meaning they take entire relations or relvars as operands, not just individual tuples.) Thus, INSERT inserts a set of tuples into the target relvar; DELETE deletes a set of tuples from the target relvar; and UPDATE updates a set of tuples in the target relvar. Now, it’s true that we often talk in terms of (for example) updating some individual tuple as such, but you need to understand that:
Such talk really means the set of tuples we’re updating just happens to have cardinality one.
What’s more, updating a set of tuples of cardinality one sometimes isn’t possible anyway.
For example, suppose relvar S is subject to the integrity constraint (see Chapter 8) that suppliers S1 and S4 are always in the same city. Then any “single tuple UPDATE” that tries to change the city for just one of those two suppliers will necessarily fail. Instead, we must change them both at the same time, perhaps like this:
What’s being updated in this example is a set of two tuples.
One consequence of the foregoing is that there’s nothing in the relational model corresponding to SQL’s “positioned updates” (i.e., UPDATE or DELETE “WHERE CURRENT OF cursor”), because those operations are tuple level (or row level, rather), not set level, by definition. They do happen to work, most of the time, in today’s SQL products, but that’s because those products aren’t very good at supporting integrity constraints. If they were to improve in that regard, those “positioned updates” might not work any more; that is, applications that succeed today might fail tomorrow—not a very desirable state of affairs, it seems to me. Recommendation: Don’t do SQL updates through a cursor, unless you can be absolutely certain that problems like the one in the example will never arise. (I say this in full knowledge of the fact that many SQL updates are done through a cursor at the time of writing.) Note: For another argument against updating through cursors, see Exercise 4.5 in Chapter 4.
Now I need to ’fess up to something. The fact is, to talk as I’ve been doing of “updating a tuple”—or set of tuples, rather—is very imprecise (not to say sloppy) anyway. Recall the definitions of value and variable from Chapter 1. If V is subject to update, then V must be a variable, by definition—but tuples (like relations) are values and can’t be updated, again by definition. What we really mean when we talk of updating tuple t1 to t2 (say), within some relvar R, is that we’re replacing tuple t1 in R by another tuple t2. And that kind of talk is still sloppy!—what we really mean is that we’re replacing the relation r1 that’s the original value of R by another relation r2. And what exactly is relation r2 here? Well, let s1 and s2 be relations containing just tuple t1 and tuple t2, respectively; then r2 is (r1 MINUS s1) UNION s2. In other words, “updating tuple t1 to t2 in relvar R” can be thought of as, first, deleting t1 and then inserting t2—if despite everything I’ve been saying you’ll let me talk in terms of deleting and inserting individual tuples in this loose fashion.
In the same kind of way, it doesn’t really make sense to talk in terms of “updating attribute A within tuple t”—or within relation r, or even within relvar R. Of course, we do it anyway, because it’s convenient (it saves a lot of circumlocution); I mean, we say things like “update the city for supplier S1 from London to New York”; but it’s like that business of user friendly terminology I discussed in Chapter 1—it’s OK to talk this way only if we all understand that such talk is only an approximation to the truth, and indeed that it tends to obscure the essence of what’s really going on.
The fact that updating is set level implies among other things that “referential triggered actions” such as ON DELETE CASCADE (see the section MORE ON FOREIGN KEYS later in this chapter)—more generally, triggered actions of all kinds— mustn’t be done until all of the explicitly requested updating has been done. In other words, a set level update must not be treated as a sequence of individual tuple level updates (or row level updates, in SQL). SQL, however, unfortunately does treat set level updates as a sequence of row level ones, at least in its support for “row level triggers” if nowhere else. Recommendation: Try to avoid operations that are inherently row level. Of course, this recommendation doesn’t prohibit set level operations in which the set just happens to be of cardinality one, as in the following example:
The fact that updating is set level has another implication too: namely, that integrity constraint checking also mustn’t be done until all of the updating (including triggered actions, if any) has been done. (The constraint discussed earlier, involving a change to the city for suppliers S1 and S4, illustrates this point very clearly. See Chapter 8 for further discussion.) Again, therefore, a set level update mustn’t be treated as a sequence of individual tuple level updates (or row level updates, in SQL). Now, I believe the SQL standard does conform to this requirement—or maybe not; its row level triggers might be a little suspect in this regard (see the subsection immediately preceding this one). In any case, even if the standard does conform, that’s not to say all commercial products do;[61] thus, you should still be on your lookout for violations in this connection.
The net of the discussions in this section overall is that update operations—in fact, all operations—in the relational model are always semantically atomic; that is, either they execute in their entirety, or they have no effect at all (except possibly for returning a status code or equivalent). Thus, although we do sometimes describe some set level operation, informally, as if it were shorthand for a sequence of tuple level operations, it’s important to understand that such descriptions are (as I said before) strictly incorrect and only approximations to the truth.