Recall this example from Chapter 10 (a logical formulation of the constraint that there’s exactly one supplier for each shipment):

     CONSTRAINT CX6 FORALL SPX ( UNIQUE SX ( SX.SNO = SPX.SNO ) ) ;

Recall too that the logic expression

     EXISTS SX ( bx )

maps to the SQL expression

     EXISTS ( SELECT * FROM S AS SX WHERE ( sbx ) )

where sbx is the SQL analog of the boolean expression bx. However, the logic expression

     UNIQUE SX ( bx )

does not map to the SQL expression

     UNIQUE ( SELECT * FROM S AS SX WHERE ( sbx ) )

(There’s an obvious trap for the unwary here.) Instead, it maps to:

     UNIQUE ( SELECT k FROM S AS SX WHERE ( sbx ) )
     AND
     EXISTS ( SELECT * FROM S AS SX WHERE ( sbx ) )

where k denotes an arbitrary constant value.^[160] (The UNIQUE invocation says there’s at most one, the EXISTS invocation says there’s at least one—where by “one” I mean one row in table S for which the boolean expression sbx evaluates to TRUE.) So constraint CX6 might map to:

     CREATE ASSERTION CX6 CHECK
          ( NOT EXISTS
              ( SELECT *
                FROM   SP AS SPX
                WHERE  NOT UNIQUE
                     ( SELECT SX.SNO
                       FROM   S AS SX
                       WHERE  SX.SNO = SPX.SNO )
                OR     NOT EXISTS
                     ( SELECT SX.SNO
                       FROM   S AS SX
                       WHERE  SX.SNO = SPX.SNO ) ) ) ;

Note: As in one of the examples in Chapter 10, the UNIQUE invocation here—even though it might not look like it—is in fact of the form UNIQUE (SELECT constant FROM ...), thanks to the boolean expression in the inner WHERE clause.^[161]

Incidentally, I think this example illustrates very well my claim that the SQL formulations produced by the techniques I’m describing in this chapter can be hard to understand. The foregoing constraint might be transcribed into stilted natural language like this:

Well, I don’t know about you, but I think it’s far from immediately obvious that this extremely tortuous sentence is logically equivalent to the following one:

By the way, there’s another equivalence we might appeal to here—the logic expression UNIQUE SX (bx) is clearly equivalent (as we saw in Chapter 10) to:

     COUNT ( SX WHERE ( bx ) ) = 1

As a result we can simplify the foregoing SQL CREATE ASSERTION to:

     CREATE ASSERTION CX6 CHECK
          ( NOT EXISTS
              ( SELECT *
                FROM   SP AS SPX
                WHERE  ( SELECT COUNT ( * )
                         FROM   S AS SX
                         WHERE  SX.SNO = SPX.SNO ) <> 1 ) ) ;

Here for interest is yet another SQL formulation, one that uses neither UNIQUE nor COUNT. Try to convince yourself it’s correct.

     CREATE ASSERTION CX6 CHECK
          ( NOT EXISTS
              ( SELECT *
                FROM   SP AS SPX
                WHERE  NOT EXISTS
                     ( SELECT *
                       FROM   S AS SX
                       WHERE  SX.SNO = SPX.SNO
                       AND    NOT EXISTS
                            ( SELECT *
                              FROM   S AS SY
                              WHERE  SY.SNO = SX.SNO
                              AND  ( SY.SNAME <> SX.SNAME OR
                                     SY.STATUS <> SX.STATUS OR
                                     SY.CITY <> SX.CITY ) ) ) ) ) ;

Note carefully, however, that this formulation relies on the fact that duplicate rows are prohibited (in table S in particular); it doesn’t work otherwise. Avoid duplicate rows!