Relational Theory for Computer Professionals

Chapter 13. SQL Constraints

The Golden Rule will keep the database

In the Golden State, which is a state of grace

—Anon.: Where Bugs Go

Recall from Chapter 6 that an integrity constraint, or just a constraint for short, is, loosely, a boolean expression that must evaluate to TRUE (because otherwise there would be something wrong with the database). Also recall The Golden Rule, which says that all integrity constraints must be satisfied at statement boundaries; in other words, the individual statement is “the unit of integrity,” and no statement—in particular, no update statement—must ever leave the database in an inconsistent state. In this chapter, we’ll take a look at the relevant features of SQL.

Database constraints

Database constraints in SQL are defined by means of CREATE ASSERTION, which is SQL’s counterpart to Tutorial D’s CONSTRAINT statement. In Chapter 6, I discussed five possible “business rules” and showed how they could be formulated using CONSTRAINT statements; now let’s see what CREATE ASSERTION analogs of those CONSTRAINT statements might look like. Note: For purposes of comparison, I’ll show the original Tutorial D formulations as well.

1. Supplier status values must be in the range 1 to 100 inclusive. In Tutorial D:

   CONSTRAINT CX1 IS_EMPTY ( S WHERE STATUS < 1 OR STATUS > 100 ) ;

   Here’s an SQL analog:

   CREATE ASSERTION CX1
          CHECK ( NOT EXISTS ( SELECT * FROM S
                               WHERE STATUS < 1 OR STATUS > 100 ) ) ;

As this example shows, a CREATE ASSERTION statement consists of (a) the keywords ...