Database Constraints

Now let me show how the ideas I've been discussing can be used in the formulation of database constraints, using examples from Chapter 6. Please note that the formulations shown aren't the only ones possible, in general.

Example 1

Status values must be in the range 1 to 100 inclusive.

     CONSTRAINT C1        FORALL s ( IF s 
 S THEN s.STATUS  0 AND  100 ) ; 

("For all possible values s, if s is a tuple in relvar S, then the STATUS value within that tuple must be in the specified range"; note the dot-qualified attribute references, and recall from Chapter 5 that "s S can be read as "s [is] in S").

Now, I'm sure you can see that many constraints are going to involve a proposition of the general form just illustrated:

     FORALL t ( IF t  p ) 

(where p is some predicate). We can simplify such propositions slightly by defining the following as a shorthand:

     FORALL t  p ) 

The bound variable t here is said to range over (the current value of) relvar R, and the expression FORALL t range-coupled quantifier. Using this shorthand, constraint C1 becomes:

     CONSTRAINT C1        FORALL s  S (  0 AND  100 ) ; 

In the same kind of way, it's helpful to define the expression:

     EXISTS t  p ) 

to be shorthand for the following:

     EXISTS t ( ( t  p ) ) 

In fact, we can (and I will) simplify matters still further, by allowing the ranges for bound variables to be defined external to the expressions that use those bound variables. For example:

     s RANGES OVER { S } 

Now I can reduce constraint C1 to just:^[*]

^[*] SQL allows a simple constraint like this one to be reduced still further by (in effect) eliding the quantifier. To be precise, we can specify a "CHECK constraint" as part of the definition of base table S that looks like this: CONSTRAINT C1 CHECK (STATUS >= 0 AND STATUS <= 100). We can even elide that "CONSTRAINT C1" as well, if we want.

     CONSTRAINT C1        FORALL s ( s.STATUS  0 AND  100 ) ; 

For the remainder of this section (and the next), I'll assume the following range definitions are in effect:

     s RANGES OVER { S }     x RANGES OVER { S }     y RANGES OVER { S }     p RANGES OVER { P }     sp RANGES OVER { SP } 

I'll also introduce additional bound variables as and when necessary.

Example 2

Suppliers in London must have status 20.

     CONSTRAINT C2        FORALL s ( IF s.CITY = 'London' THEN s.STATUS = 20 ) ; 

Example 3

Supplier numbers are unique.

     CONSTRAINT C3        FORALL x, y ( IF x.SNO = y.SNO THEN x.SNAME  = y.SNAME                                       AND  x.STATUS = y.STATUS                                       AND  x.CITY   = y.CITY ) ; 

The quantifier FORALL x,y is shorthand for FORALL x FORALL y.

Example 4

No supplier with status less than 20 can supply part P6.

     CONSTRAINT C4        FORALL s ( IF s.STATUS < 20 THEN           FORALL sp ( IF sp.SNO = s.SNO THEN                          sp.PNO  PNO('P6) ) ) ; 

Example 5

Every shipment must have a supplier.

     CONSTRAINT C5        FORALL sp ( EXISTS s ( sp.SNO = s.SNO ) ) ; 

Example 6

No supplier number appears in both relvar LS and relvar NLS.

     CONSTRAINT C6        FORALL m  LS FORALL  NLS (  
Example 7
 
Supplier S1 and part P1 must never be in different cities.
 
     CONSTRAINT C7        NOT ( EXISTS s EXISTS p ( s.SNO = SNO('S1') AND                                  p.PNO = PNO('P1') AND                                  s.CITY  I'll show a few more examples (not based on examples in Chapter 6) in order to illustrate some additional points.
 

Example 8
 
There must always be at least one supplier.
 
     CONSTRAINT C8 EXISTS s ( TRUE ) ; 

 
 

Example 9
 
All parts are either blue or green, and there exists at least one part.
 
     CONSTRAINT C9        FORALL p ( p.COLOR = 'Blue' OR p.COLOR = 'Green' ) AND        EXISTS p ( TRUE ) ; 

 
Note the use of two distinct bound variables here, both called p.
 

Example 10
 
All parts are either blue or green, or there exists at least one red part.
 
     CONSTRAINT C10        FORALL p ( p.COLOR = 'Blue' OR p.COLOR = 'Green' ) OR        EXISTS p ( p.COLOR = 'Red' ) ;