The Principle of Interchangeability implies that views not only have relvar predicates like base relvars, they also have relvar constraints like base relvars—by which I mean they have both individual relvar constraints and what in Chapter 8 I called a total relvar constraint (for the relvar in question). As with predicates, however, the constraints that apply to a view V are derived: They’re derived from the constraints for the relvars in terms of which V is defined, in accordance with the semantics of the relational operations involved in the defining expression. By way of example, consider view LS once again. That view is a restriction of relvar S—i.e., its defining expression specifies a restriction operation on relvar S—and so its (total) relvar constraint is the logical AND of the (total) relvar constraint for S and the specified restriction condition. Let’s suppose for the sake of the example that the only constraint that applies to base relvar S is the constraint that {SNO} is a key. Then the total relvar constraint for view LS is the AND of that key constraint and the constraint that the city is London, and view LS is required to satisfy that constraint at all times. (In other words, The Golden Rule applies to views just as it does to base relvars.)
For simplicity, from this point forward I’ll use the term view constraint to refer to any constraint that applies to some view. Now, just because view constraints are always derived in the sense explained above, it doesn’t follow that there’s no need to declare them explicitly. For one thing, the system might not be “intelligent” enough to carry out the inferences needed to determine for itself the constraints that apply to some view; for another, such explicit declarations can at least serve documentation purposes (i.e., they can help explain the semantics of the view in question to users, if not to the system); and there’s another reason too, which I’ll get to in a little while.
I claim, then, that it should be possible to declare explicit constraints for views. In particular, it should be possible (a) to include explicit KEY and FOREIGN KEY specifications in view definitions and (b) to allow the target relvar in a FOREIGN KEY specification to be a view. Here’s an example to illustrate possibility (a):
VAR LS VIRTUAL ( S WHERE CITY = 'London' )
KEY { SNO } ;Tutorial D does permit such specifications; SQL doesn’t. Recommendation: In SQL, include such specifications in the form of comments. For example:
CREATE VIEW LS
AS ( SELECT S.*
FROM S
WHERE S.CITY = 'London' )
/* UNIQUE ( SNO ) */
WITH CHECK OPTION ;Note: As I’ve said, SQL doesn’t permit view constraints to be formulated explicitly as part of the view definition; however, logically equivalent constraints can always be formulated by means of CREATE ASSERTION (if it’s supported, that is). More generally, in fact, CREATE ASSERTION allows us to formulate constraints of any kind we like for any table that could be a view if we chose to define it as such—in other words, for any table that can be defined by some arbitrarily complex table expression (which is to say, any table at all).[128] I’ll have more to say about this possibility in a few moments.
Now, having said that it should be possible to declare explicit constraints on views, I should now add that sometimes it might be a good idea not to, because it could lead to redundant checking. For example, as I’ve said, the specification KEY{SNO} clearly applies to view LS—but that’s because it applies to base relvar S as well,[129] and declaring it explicitly for view LS might simply lead to the same constraint being checked twice. (But it should still be stated as part of the view documentation, somehow, because it’s part of the semantics of the view.)
Perhaps more to the point, there certainly are situations where declaring view constraints explicitly could be a good idea. Here’s an example, expressed in SQL for definiteness. We’re given two base tables that look like this (in outline):
CREATE TABLE FDH
( FLIGHT ... ,
DESTINATION ... ,
HOUR ... ,
UNIQUE ( FLIGHT ) ) ;
CREATE TABLE DFGP
( DAY ... ,
FLIGHT ... ,
GATE ... ,
PILOT ... ,
UNIQUE ( DAY , FLIGHT ) ) ;The tables have predicates as follows:[130]
FDH: Flight FLIGHT leaves at hour HOUR for destination DESTINATION.
DFGP: On day DAY, flight FLIGHT with pilot PILOT leaves from gate GATE.
They’re subject to the following constraints (expressed here in a kind of pseudo logical style):
IF (f1,n1,h) , (f2,n2,h) ∈ FDH AND (d,f1,g,p1) , (d,f2,g,p2) ∈ DFGP THENf1=f2ANDp1=p2 /* and n1 = n2, incidentally */IF (f1,n1,h) , (f2,n2,h) ∈ FDH AND (d,f1,g1,p) , (d,f2,g2,p) ∈ DFGP THENf1=f2ANDg1=g2 /* and n1 = n2, incidentally */
Explanation: The first of these constraints says:
If two rows of FDH, one for flight f1 (with destination n1) and one for flight f2 (with destination n2), have the same HOUR h, and
Two rows of DFGP, one each for the FLIGHTs f1 and f2 from the two FDH rows, have the same DAY d and GATE g, then
The two FDH rows must be the same and the two DFGP rows must be the same. In other words, if we know the HOUR, DAY, and GATE, then the FLIGHT and PILOT (and DESTINATION) are determined.
The second constraint is analogous:
If two rows of FDH, one for flight f1 (with destination n1) and one for flight f2 (with destination n2), have the same HOUR h, and
Two rows of DFGP, one each for the FLIGHTs f1 and f2 from the two FDH rows, have the same DAY d and PILOT p, then
The two FDH rows must be the same and the two DFGP rows must be the same—in other words, if we know the HOUR, DAY, and PILOT, then the FLIGHT and GATE (and DESTINATION) are determined.
Now, stating these constraints directly in terms of the two base tables is fairly nontrivial:
CREATE ASSERTION BTCX1 CHECK
( NOT ( EXISTS ( SELECT * FROM FDH AS FX WHERE
EXISTS ( SELECT * FROM FDH AS FY WHERE
EXISTS ( SELECT * FROM DFGP AS DX WHERE
EXISTS ( SELECT * FROM DFGP AS DY WHERE
FY.HOUR = FX.HOUR AND
DX.FLIGHT = FX.FLIGHT AND
DY.FLIGHT = FY.FLIGHT AND
DY.DAY = DX.DAY AND
DY.GATE = DX.GATE AND
( FX.FLIGHT <> FY.FLIGHT OR
DX.PILOT <> DY.PILOT ) ) ) ) ) ) ) ;
CREATE ASSERTION BTCX2 CHECK
( NOT ( EXISTS ( SELECT * FROM FDH AS FX WHERE
EXISTS ( SELECT * FROM FDH AS FY WHERE
EXISTS ( SELECT * FROM DFGP AS DX WHERE
EXISTS ( SELECT * FROM DFGP AS DY WHERE
FY.HOUR = FX.HOUR AND
DX.FLIGHT = FX.FLIGHT AND
DY.FLIGHT = FY.FLIGHT AND
DY.DAY = DX.DAY AND
DY.PILOT = DX.PILOT AND
( FX.FLIGHT <> FY.FLIGHT OR
DX.GATE <> DY.GATE ) ) ) ) ) ) ) ;But stating them in the form of key constraints on a view definition, if that were permitted, would take care of matters nicely:
CREATE VIEW V AS
( SELECT * FROM FDH NATURAL JOIN DFGP ,
UNIQUE ( DAY , HOUR , GATE ) , /* hypothetical */
UNIQUE ( DAY , HOUR , PILOT ) ) ; /* syntax !!!!! */Since this solution isn’t available, we should at least specify those hypothetical view constraints in terms of suitable assertions:
CREATE VIEW V AS ( SELECT * FROM FDH NATURAL JOIN DFGP ) ;
CREATE ASSERTION VCX1
CHECK ( UNIQUE ( SELECT DAY , HOUR , GATE FROM V ) ) ;
CREATE ASSERTION VCX2
CHECK ( UNIQUE ( SELECT DAY , HOUR , PILOT FROM V ) ) ;In fact, of course, we don’t actually have to define the view V in order to define these constraints—we could simply replace the references to view V in the UNIQUE expressions in the constraints by the defining expression for V, like this:[131]
CREATE ASSERTION VCX1
CHECK ( UNIQUE ( SELECT DAY , HOUR , GATE
FROM FDH NATURAL JOIN DFGP ) ) ;
CREATE ASSERTION VCX2
CHECK ( UNIQUE ( SELECT DAY , HOUR , PILOT
FROM FDH NATURAL JOIN DFGP ) ) ;Note: I didn’t mention the point in Chapter 8, but Tutorial D does provide direct support for saying the relation denoted by some relational expression is required to satisfy some key constraint. By way of illustration, here are Tutorial D analogs of assertions VCX1 and VCX2:
CONSTRAINT VCX1 ( FDH JOIN DFGP ) KEY { DAY , HOUR , GATE } ;
CONSTRAINT VCX2 ( FDH JOIN DFGP ) KEY { DAY , HOUR , PILOT } ;[128] Any table, that is, so long as the definition of the table in question doesn’t involve a possibly nondeterministic expression, a complication I choose to ignore for now. See Chapter 12 for further discussion.
[129] A more accurate statement is: The specification KEY{SNO} applies to view LS as a logical consequence of the fact that it applies to base relvar S. Note, however, that the two specifications don’t mean quite the same thing—the one for view LS means suppliers numbers are unique with respect to London suppliers, the one for base relvar S means they’re unique with respect to all suppliers.
[130] The tables are rather obviously not very well designed, and thus you might think I’m “stacking the deck” in an attempt to make my point seem more convincing than it really is. So I’d like to say the example isn’t in fact mine at all; rather, it’s a lightly edited version of one from Joe Celko’s article “Back to the Future” (Database Programming & Design 4, No. 12, December 1991).
[131] If you look carefully, you’ll see I’m not exactly replacing those references to V by the defining expression for V. The reason is that (as we saw in Chapter 6) SQL requires an explicit JOIN invocation like FDH NATURAL JOIN DFGP to have a “SELECT * FROM” prefix if it appears at the outermost level of nesting but allows it not to have such a prefix otherwise.