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 ( SELECTk
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 ...
Get SQL and Relational Theory, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.