Now, I was practicing a tiny deception in Example 1, inasmuch as I was pretending that the specific part to which the constraint applied was understood. But that’s effectively just what happens with base table constraints in SQL; they’re tacitly understood to apply to each and every row of the base table whose definition they’re part of. However, suppose we wanted to be more explicit—i.e., suppose we wanted to state explicitly that the constraint applies to every part that happens to be represented in table P. In other words, for all such parts PX, if the color of part PX is red, then the city for part PX is London:
FORALL PX ( IF PX.COLOR = 'Red' THEN PX.CITY = 'London' )
Note: The name PX and others like it in this chapter are deliberately chosen to be reminiscent of the range variables used in examples in the previous chapter. In fact, I’m going to assume from this point forward that names of the form PX, PY, etc., denote variables that range over the current value of table P; names of the form SX, SY, etc., denote variables that range over the current value of table S; and so on.[156] Details of how such variables are defined—in logic, I mean, not in SQL—aren’t important for present purposes and are therefore omitted. In SQL, they’re defined by means of AS clauses, which I’ll show when we get to the SQL formulations as such.
Now, SQL doesn’t support FORALL, but the quantification law tells us that the foregoing expression can be transformed into this one:
NOT EXISTS PX ( NOT ( IF PX.COLOR = 'Red' THEN PX.CITY = 'London' ) )
(Again I’ve added some parentheses for clarity. From this point forward, in fact, I’ll feel free to introduce or drop parentheses as and when I feel it’s desirable to do so, without further comment.) Now applying the implication law:
NOT EXISTS PX ( NOT ( NOT ( PX.COLOR = 'Red' ) OR PX.CITY = 'London' ) )
This expression could now be mapped directly into SQL, but it’s probably worth tidying it up a little first. Applying De Morgan:
NOT EXISTS PX ( NOT ( NOT ( ( PX.COLOR = 'Red' )
AND NOT ( PX.CITY = 'London' ) ) ) )Applying the double negation law and dropping some parentheses:
NOT EXISTS PX ( PX.COLOR = 'Red' AND NOT ( PX.CITY = 'London' ) )
Finally:
NOT EXISTS PX ( PX.COLOR = 'Red' AND PX.CITY ≠ 'London' )
Now, the transformations so far have all been very simple; you might even have found them rather tedious. But mapping this final logical expression into SQL isn’t quite so straightforward. Here are the details of that mapping:
First of all, NOT maps to NOT (unsurprisingly).
Second, “EXISTS PX (bx)” maps to “EXISTS (SELECT * FROM P AS PX WHERE (bx′)),” where bx′ is the SQL analog of the boolean expression bx. Note: Of course, mapping bx to bx′ might require further (recursive) application of these rules.
Third, the parentheses surrounding sbx can be dropped, though they don’t have to be.
Last, the entire expression needs to be wrapped up inside some suitable CREATE ASSERTION syntax.
So here’s the final version:
CREATE ASSERTION ... CHECK
( NOT EXISTS ( SELECT *
FROM P AS PX
WHERE PX.COLOR = 'Red'
AND PX.CITY <> 'London' ) ) ;[156] I’m being sloppy here. The phrase “range over table P” ought really to be “range over the table value that’s the current value of the table variable called P” (and similarly for “range over table S,” of course). But SQL has no explicit notion of table values vs. table variables.