SQL and Relational Theory, 2nd Edition

TYPE CONSTRAINTS IN SQL

As I’m sure you noticed, I didn’t give SQL versions of the examples in the previous section. That’s because, believe it or not, SQL doesn’t support type constraints at all!—apart from the rather trivial a priori ones, of course. For example, although SQL would certainly let you create a user defined type called QTY and specify that quantities must be representable as integers, it wouldn’t let you say those integers must lie in a certain range. In other words, an SQL definition for that type might look like this:

     CREATE TYPE QTY AS INTEGER FINAL ;

(The keyword FINAL here just means type QTY doesn’t have any proper subtypes. Subtypes in general are beyond the scope of this book.)

With the foregoing SQL definition, all available integers (including negative ones!) will be regarded as denoting valid quantities. If you want to constrain quantities to some particular range, therefore, you’ll have to specify an appropriate database constraint—in practice, probably a base table constraint (see the section DATABASE CONSTRAINTS IN SQL)—on each and every use of the type. For example, if column QTY in base table SP is defined to be of type QTY instead of type INTEGER, then you might need to extend the definition of that table as follows (note the CONSTRAINT specification at the end):

 CREATE TABLE SP ( SNO VARCHAR(5) NOT NULL , PNO VARCHAR(6) NOT NULL , QTY QTY NOT NULL , UNIQUE ( SNO , PNO ) , FOREIGN KEY ( SNO ) REFERENCES S ( SNO ) , FOREIGN KEY ( PNO ) REFERENCES P ...