SQL and Relational Theory, 2nd Edition

EXAMPLE 1: LOGICAL IMPLICATION

Consider again the constraint from the previous chapter to the effect that all red parts must be stored in London. For a given part, this constraint corresponds to a business rule that might be stated more or less formally like this:

     IF COLOR = 'Red' THEN CITY = 'London'

In other words, it’s a logical implication. Now, SQL doesn’t support logical implication as such, but the implication law tells us that the foregoing expression can be transformed into this one:

     ( NOT ( COLOR = 'Red' ) ) OR ( CITY = 'London' )

(I’ve added some parentheses for clarity.) And this expression involves only operators that SQL does support, so it can be formulated directly as a base table constraint:

     CONSTRAINT BTCX1 CHECK ( NOT ( COLOR = 'Red' ) OR ( CITY = 'London' ) )

Or perhaps a little more naturally, making use of the fact that NOT (a = b) can be transformed into a ≠ b—in SQL, a <> b—and dropping unnecessary parentheses (in other words, applying some further simple transformations):

     CONSTRAINT BTCX1 CHECK ( COLOR <> 'Red' OR CITY = 'London' )

Note: I’ve said that SQL doesn’t support logical implication (IF ... THEN ...) as such. That’s true. But it does support CASE expressions, and so this first example might alternatively be formulated in SQL as follows:

     CONSTRAINT BTCX1 CHECK ( CASE
                                 WHEN COLOR = 'Red' THEN CITY = 'London'
                                 ELSE TRUE
                              END ) ;

In general, the logical implication IF p THEN q can be mapped into the SQL CASE expression CASE WHEN p′ THEN q′ ELSE TRUE END, where ...