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 ( PNO ) ,
            CONSTRAINT SPQC CHECK ( QTY >= QTY(0) AND
                                    QTY <= QTY(5000) ) ) ;

The expressions QTY(0) and QTY(5000) in the CONSTRAINT specification here can be regarded as QTY selector invocations. I remind you, however, that selector isn’t an SQL term (and nor is THE_ operator); as indicated in Chapter 2, in fact, the situation regarding selectors and THE_ operators in SQL is too complicated to describe in detail in this book. Suffice it to say that analogs of those operators are usually available, though they aren’t always provided “automatically” as they are in Tutorial D.

For interest, I also show an SQL definition for type POINT (and here I’ve specified NOT FINAL instead of FINAL, just to illustrate the possibility):

     CREATE TYPE POINT AS
          ( X NUMERIC(5,1) , Y NUMERIC(5,1) ) NOT FINAL ;

To say it again, then, SQL doesn’t really support type constraints. The reasons for the omission are complex—they have to do with type inheritance and are therefore beyond the scope of this book—but the implications are serious. Recommendation: Wherever possible, use database constraints to make up for the omission, as in the QTY example above. Of course, this recommendation might lead to a lot of duplicated effort, but such duplication is better than the alternative: namely, bad data in the database. See Exercise 8.8 at the end of the chapter.