Relational assignment in general works by assigning a relation value, denoted by some relational expression, to a relation variable, denoted by a relvar reference (where a relvar reference is basically just the pertinent relvar name). Here’s a Tutorial D example:
S := S WHERE NOT ( CITY = 'Athens' ) ;
Now, it’s easy to see that this particular assignment is logically equivalent to the following DELETE statement:
DELETE S WHERE CITY = 'Athens' ;
More generally, the Tutorial D DELETE statement
DELETERWHEREbx;
(where R is a relvar name and bx is a boolean expression) is shorthand for, and hence logically equivalent to, the following relational assignment:
R:=RWHERE NOT (bx) ;
Alternatively, we might say it’s shorthand for this one (either way, it comes to the same thing):
R:=RMINUS (RWHEREbx) ;
Turning to INSERT, the Tutorial D INSERT statement
INSERT R rx ;(where R is again a relvar name and rx is a relational expression—typically but not necessarily a relation selector invocation) is shorthand for:
R:=RUNIONrx;
For example, the INSERT statement—
INSERT SP RELATION { TUPLE { SNO 'S5' , PNO 'P6' , QTY 700 } } ;—effectively inserts a single tuple into the shipments relvar SP.
Finally, the Tutorial D UPDATE statement also corresponds to a certain relational assignment. However, the details are a little more complicated in this case than they are for INSERT and DELETE, and I’ll defer them to Chapter 7.
I’ve said the INSERT statement
INSERT R rx ;is shorthand for:
R:=RUNIONrx;
Observe now, however, that this definition implies that an attempt to insert “a tuple that already exists” (i.e., an INSERT in which the relations denoted by R and rx aren’t disjoint) will succeed. (It won’t insert a duplicate tuple, of course—it just won’t have any effect.) For that reason, Tutorial D additionally supports an operator called D_INSERT (“disjoint INSERT”), with syntax as follows:
D_INSERT R rx ;This statement is shorthand for:
R:=RD_UNIONrx;
D_UNION here stands for disjoint union. Disjoint union is just like regular union, except that its operand relations are required to have no tuples in common (see Chapter 6). It follows that an attempt to use D_INSERT to insert a tuple that already exists will fail.
What about DELETE? Well, observe first that the syntax presented above—
DELETERWHEREbx;
—is actually just a special case (though it’s far and away the commonest case in practice). The more general form parallels the syntax of INSERT:
DELETE R rx ;Here R is a relvar name and rx is a relational expression (possibly but not necessarily a relation selector invocation).[62] This more general form of DELETE is defined to be shorthand for:
R:=RMINUSrx;
For example, the DELETE statement—
DELETE SP RELATION { TUPLE { SNO 'S1' , PNO 'P1' , QTY 300 } } ;—effectively deletes a single tuple from the shipments relvar SP.
It should be clear, however, that the foregoing definition implies that an attempt to delete “a tuple that doesn’t exist” (i.e., a DELETE in which the relation denoted by rx isn’t wholly included in the relation denoted by R) will succeed. For that reason, Tutorial D additionally supports an operator called I_DELETE (“included DELETE”), with syntax as follows:
I_DELETE R rx ;This statement is shorthand for:
R:=RI_MINUSrx;
I_MINUS here stands for included minus; the expression r1 I_MINUS r2 is defined to be the same as r1 MINUS r2 (see Chapter 6), except that every tuple appearing in r2 must also appear in r1—in other words, r2 must be included in r1. It follows that an attempt to use I_DELETE to delete a tuple that doesn’t exist will fail.
Note: Now that I’ve introduced D_INSERT and I_DELETE, please understand that discussions elsewhere in this book that refer to INSERT and DELETE operations in Tutorial D should be taken for simplicity as applying to D_INSERT and I_DELETE operations as well, where the sense demands it.
SQL has nothing directly comparable to Tutorial D’s D_INSERT and I_DELETE. Apart from this difference, however, SQL’s support for INSERT, DELETE, and UPDATE operations resembles that of Tutorial D fairly closely and there’s little more to be said, except for a few points regarding INSERT specifically:
First, the source for an SQL INSERT operation is specified by means of a table expression (typically but not necessarily a VALUES expression—see Chapter 3). Contrary to popular opinion, therefore, INSERT in SQL really does insert a table, not a row, though that table (the source table) might and often will contain just one row, or even no rows at all.
Second, INSERT in SQL is defined in terms of neither UNION nor D_UNION, but rather in terms of SQL’s “UNION ALL” operator (see Chapter 6). As a consequence, an attempt to insert a row that already exists will fail if the target table is subject to a key constraint but will succeed (and will insert a duplicate row) otherwise.
Third, INSERT in SQL supports an option according to which the target table specification can be followed by a parenthesized column name commalist, identifying the columns into which values are to be inserted; the ith target column corresponds to the ith column of the source table. Omitting this option is equivalent to specifying all of the columns of the target table, in the left to right order in which they appear within that table. Recommendation: Never omit this option. For example, the INSERT statement
INSERT INTO SP ( PNO , SNO , QTY ) VALUES ( 'P6' , 'S5' , 700 ) ;
is preferable to this one—
INSERT INTO SP VALUES ( 'S5' , 'P6' , 700 ) ;
—because this second formulation relies on the left to right ordering of columns in table SP and the first one doesn’t.[63] Here’s another example (incidentally, this one makes it clear that INSERT really does insert a table and not a row):
INSERT INTO SP ( SNO , PNO , QTY ) VALUES ( 'S3' , 'P1' , 500 ) , ( 'S2' , 'P5' , 400 ) ;
As for relational assignment: Unfortunately SQL doesn’t have a direct counterpart to this operator. The closest it can get to the generic assignment
R:=rx;
is the following sequence of statements:
DELETE FROMT; INSERT INTOT( ... )tx;
(T and tx here are the SQL analogs of R and rx, respectively.) Note in particular that (as noted in the answer to Exercise 1.16 in Appendix F) this sequence of statements could fail where its relational counterpart, the relational assignment, would succeed—for example, if table T is subject to the constraint that it mustn’t be empty.
I’d like to close this section by drawing your attention to a principle that, though it’s really quite simple, has far reaching consequences: The Assignment Principle, which states that after assignment of value v to variable V, the comparison v = V must evaluate to TRUE. Note: The Assignment Principle is a fundamental principle, not just for the relational model, but for computing in general. It applies to relational assignment in particular, of course, but (to repeat) it’s actually relevant to assignments of all kinds. In fact, as I’m sure you realize, it’s more or less the definition of the assignment operation. I’ll have more to say about it in Chapter 8, when I discuss what’s called multiple assignment.
[62] The common special case “DELETE R WHERE bx;” can be thought of as shorthand for “DELETE R (R WHERE bx);”.
[63] Even though this tactic—i.e., specifying the option—does fix the problem at hand, I’d like to inject here a comment that Hugh Darwen once made to me (in a private communication): “The syntax of a language should in all places be in the spirit of that language. Then it’s easier to learn, because people get to know what to expect. A proper relational language attaches no significance to column ordering. Not anywhere.”