SQL and Relational Theory, 2nd Edition

DATABASE CONSTRAINTS IN SQL

Any constraint that can be formulated by means of a CONSTRAINT statement in Tutorial D can be formulated by means of a CREATE ASSERTION statement in SQL, as examples CX1-CX6 in the previous section should have been sufficient to suggest.^[117] Unlike Tutorial D, however, SQL has a feature according to which any such constraint can alternatively be specified as part of the definition of some base table—i.e., as a base table constraint. For example, here again is the SQL version (using CREATE ASSERTION) of constraint CX5 from the previous section:

     CREATE ASSERTION CX5 CHECK
          ( NOT EXISTS ( SELECT *
                         FROM   S NATURAL JOIN SP
                         WHERE  STATUS < 20
                         AND    PNO =  'P6' ) ) ;

This example could have been stated in slightly different form as a base table constraint as part of the definition of base table SP, like this:

     CREATE TABLE SP
          ( ... ,
            CONSTRAINT CX5 CHECK  /* "base table" constraint */
                     ( PNO <> 'P6' OR ( SELECT STATUS FROM S
                                        WHERE  SNO = SP.SNO ) >= 20 ) ) ;

Note, however, that a logically equivalent formulation could have been specified as part of the definition of base table S instead—or base table P, or absolutely any base table in the database, come to that (see Exercise 8.17 at the end of the chapter).

Now, this alternative style can be useful for row constraints (i.e., constraints that can be checked for an individual row in isolation), because it’s a little simpler than its CREATE ASSERTION counterpart. Here, for example, are constraints CX1 and CX2 from the previous ...