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 section, reformulated as base table constraints on base table S:
CREATE TABLE S
( ... ,
CONSTRAINT CX1 CHECK ( STATUS >= 1 AND STATUS <= 100 ) ) ;
CREATE TABLE S
( ... ,
CONSTRAINT CX2 CHECK ( STATUS = 20 OR CITY <> 'London' ) ) ;For a constraint involving more than one base table, however, CREATE ASSERTION is usually better, because it avoids having to make an arbitrary choice as to which table to attach the constraint to.
Note: Certain constraints—for example, NOT NULL constraints and key constraints for keys that involve just one column—can optionally be formulated as “column constraints” in SQL. A column constraint is one that’s specified, not just as part of the definition of the base table in question, but as part of the definition of some specific column of that base table. For simplicity, I’ll ignore this possibility in this book, except for NOT NULL constraints in particular.
Two last points to close this section:
Be aware that any constraint stated as part of the CREATE TABLE for base table T is automatically satisfied if T is empty—even if the constraint is of the form “T mustn’t be empty”! (Or even if it’s of the form “T must contain −5 rows,” or the form “1 = 0,” come to that.) See Exercises 8.15 and 8.16 at the end of the chapter.
(Important!) While most current SQL products do support key and foreign key constraints, they don’t support CREATE ASSERTION at all, and they don’t support base table constraints any more complicated than simple row constraints. (Formally, they don’t permit base table constraints to contain a subquery.) Recommendation: Specify constraints declaratively whenever you can. In practice, however, many constraints (perhaps most) will, regrettably, have to be enforced by means of procedural code (possibly triggered procedures)—and that code can be quite difficult to write, too.[118] This state of affairs represents a serious defect in today’s products, and it needs to be remedied, urgently.
[117] Except that (as you’ll recall from Chapter 2) SQL constraints are supposed not to contain “possibly nondeterministic expressions,” a rule that could cause serious problems in practice if true. See Chapter 12 for further discussion.
[118] In this connection, I’d like to recommend the book Applied Mathematics for Database Professionals, by Lex de Haan and Toon Koppelaars (see Appendix G)—especially Chapter 11 of that book.