SQL and Relational Theory, 2nd Edition

EXAMPLE 10: UNIQUE QUANTIFICATION

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 ...