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 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.
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 ...