O'Reilly logo

Relational Theory for Computer Professionals by Chris Date

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required