As we saw in Chapter 2, one of the things we have to do when we define a type is specify the values that make up that type. Here’s an example:

  1  TYPE QTY
  2       POSSREP QPR
  3            { Q INTEGER
  4                    CONSTRAINT Q ≥ 0 AND Q ≤ 5000 } ;

Explanation:

Here’s a slightly more complicated example:

    TYPE POINT
         POSSREP CARTESIAN { X NUMERIC, Y NUMERIC
                 CONSTRAINT SQRT ( X ** 2 + Y ** 2 ) ≤ 100.0 } ;

Type POINT denotes points in two-dimensional space; it has a possrep CARTESIAN with two numeric components called X and Y (corresponding, presumably, to cartesian coordinates), and there’s a type constraint that says, in effect, that the only points we’re interested in are those that lie on or inside a circle with center the origin and radius 100.

  TYPE POINT POSSREP { X NUMERIC, Y NUMERIC } ;

  TYPE POINT POSSREP CARTESIAN { X NUMERIC, Y NUMERIC }
             POSSREP POLAR { R NUMERIC, THETA NUMERIC } ;

  TYPE QTY POSSREP { Q INTEGER } ;

  TYPE QTY POSSREP { Q INTEGER CONSTRAINT TRUE } ;

  CREATE TYPE QTY AS INTEGER FINAL ;

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

To recap, a database constraint constrains the values that can appear in a given database. In Tutorial D, such constraints are specified by means of a CONSTRAINT statement (or by some shorthand that’s effectively equivalent to such a statement); in SQL, they’re specified by means of a CREATE ASSERTION statement (or, again, by some equivalent shorthand). I don’t want to get into details of those shorthands—at least, not yet—because they’re really just a matter of syntax; for now, let’s stay with the “longhand” forms. I’ll begin with a series of examples (Tutorial D on the left and SQL on the right, as usual).

  CONSTRAINT C1 IS_EMPTY        | CREATE ASSERTION C1 CHECK
  ( S WHERE STATUS < 1       | ( NOT EXISTS
      OR    STATUS > 100 ) ; | ( SELECT S.* FROM S
                                |   WHERE  S.STATUS < 1
                                |   OR     S.STATUS > 100 ) ) ;

  CONSTRAINT C2 IS_EMPTY        | CREATE ASSERTION C2 CHECK
  ( S WHERE CITY = 'London'     | ( NOT EXISTS
      AND   STATUS ≠ 20 ) ;  | ( SELECT S.* FROM S
                                |   WHERE  S.CITY = 'London'
                                |   AND    S.STATUS <> 20 ) ) ;

  CONSTRAINT C3             |   CREATE ASSERTION C3 CHECK
     COUNT ( S ) =          |  ( UNIQUE ( SELECT S.SNO
     COUNT ( S { SNO } ) ;  |             FROM   S ) ) ;

  UNIQUE ( SELECT DISTINCT S.SNO FROM S )

  CREATE ASSERTION C3 CHECK
   ( ( SELECT COUNT ( * ) FROM S ) =
     ( SELECT COUNT ( SNO ) FROM S ) ) ;

  CONSTRAINT C4 IS_EMPTY       | CREATE ASSERTION C4 CHECK
  ( ( S JOIN SP )              | ( NOT EXISTS
    WHERE STATUS < 20       | ( SELECT *
    AND   PNO = PNO('P6') ) ;  |   FROM   S, SP
                               |   WHERE  S.SNO = SP.SNO
                               |   AND    S.STATUS < 20
                               |   AND    SP.PNO = PNO('P6') ) ) ;

  CONSTRAINT C5                    | CREATE ASSERTION C5 CHECK
     SP { SNO } ⊆ S { SNO } ; | ( NOT EXISTS
                                   |    ( SELECT SP.SNO
                                   |      FROM   SP
                                   |      EXCEPT
                                   |      SELECT S.SNO
                                   |      FROM   S ) ) ;

Transaction theory is a large topic in its own right. As mentioned in Chapter 4, however, it doesn’t have much to do with the relational model as such (at least, not directly), and for that reason I don’t want to discuss it in detail here. In any case, you’re a database professional, and I’m sure you’re familiar with basic transaction concepts. The standard reference—highly recommended, by the way—is Transaction Processing: Concepts and Techniques, by Jim Gray and Andreas Reuter (Morgan Kaufmann, 1993). All I want to do here is briefly review the so-called ACID properties of transactions.

ACID is an acronym; it stands for atomicity - consistency - isolation - durability . Here are brief explanations:

Now, one argument in favor of transactions has always been that they’re supposed to act as “a unit of integrity” (that’s what the consistency property is all about). But I don’t believe that argument; as I’ve more or less said already, I believe that statements have to be that unit, and that database constraints must therefore be satisfied at statement boundaries. The section immediately following gives my justification for this position.

I have at least five reasons for taking the position that database constraints must be satisfied at statement boundaries. The first and biggest one is as follows. As we know from Chapter 4, a database can be regarded as a collection of propositions, propositions that we believe are true. And if that collection is ever allowed to include any inconsistencies, then all bets are off. As I’ll show in the section "Constraints and Predicates,” later in this chapter, we can never trust the answers we get from an inconsistent database! While it might be true, thanks to the isolation property, that no more than one transaction ever sees any particular inconsistency, the fact remains that that particular transaction does see the inconsistency and can therefore produce wrong answers.

Now, I think this first argument is strong enough to stand on its own, but I’ll give the other four arguments as well, for purposes of reference if nothing else. Second, then, I don’t agree that any given inconsistency can be seen by only one transaction, anyway; that is, I don’t believe in the isolation property. Part of the problem here is that the word isolation doesn’t mean the same in the world of transactions as it does in ordinary English—in particular, it doesn’t mean that transactions can’t communicate with one another. For if transaction T1 produces some result, in the database or elsewhere, that’s subsequently read by transaction T2, then T1 and T2 aren’t truly isolated from each other (and this remark applies regardless of whether T1 and T2 run concurrently or otherwise). In particular, therefore, if (a) T1 sees an inconsistent state of the database and therefore produces an incorrect result, and (b) that result is then seen by T2, then (c) the inconsistency seen by T1 has effectively been propagated to T2. In other words, it can’t be guaranteed that a given inconsistency, if permitted, will be seen by just one transaction, anyway.

Third, we surely don’t want every program (or other “code unit”) to have to cater for the possibility that the database might be inconsistent when it’s invoked. There’s a severe loss of orthogonality if a program that assumes consistency can’t be used safely while constraint checking is deferred. In other words, I want to be able to specify code units independently of whether they’re to be executed as a transaction as such or just as part of a transaction. (In fact, I’d like support for nested transactions, but that’s a topic for another day.)

Fourth, The Principle of Interchangeability (of base relvars and views—see Chapter 4) implies that the very same constraint might be a single-relvar constraint with one design for the database and a multi-relvar constraint with another. For example, recall these two views from Chapter 4:

    VAR LS  VIRTUAL ( S WHERE CITY = 'London' ) ;

    VAR NLS VIRTUAL ( S WHERE CITY ≠ 'London' ) ;

These views satisfy the constraint that no supplier number appears in both. However, there’s no need to state that constraint explicitly, because it’s implied by the single-relvar constraint that {SNO} is a key for relvar S (along with the fact that every supplier has exactly one city, which is implicit in the design of relvar S). But suppose we made LS and NLS base relvars and defined their union as a view called S. Then the constraint would have to be stated explicitly:

    CONSTRAINT C6 IS_EMPTY   |  CREATE ASSERTION C6 CHECK
     ( LS { SNO } JOIN       |  ( NOT EXISTS
      NLS { SNO } ) ;        |    ( SELECT *
                             |    FROM   LS, NLS
                             |  WHERE  LS.SNO = NLS.SNO ) ) ;

Now what was previously a single-relvar constraint has become a multi-relvar constraint instead. Thus, if we agree that single-relvar constraints must be checked immediately, we must surely agree that multi-relvar constraints must be checked immediately as well.

Fifth and last, there’s an optimization technique called semantic optimization (it involves expression transformation, but I deliberately didn’t discuss it in the section of that name in Chapter 5). For example, consider the expression (SP JOIN S){PNO}. Clearly, the join here is a foreign-to-matching-primary-key join; as a consequence, every SP tuple does join to some S tuple and therefore contributes a part number to the overall result. So there’s no need to do the join!—the expression can be simplified to just SP{PNO}. The point to note, however, is that this transformation is valid only because of the semantics of the situation; in general, each operand to a join will include some tuples that have no counterpart in the other operand and so don’t contribute to the overall result, and transformations such as the one just shown therefore won’t be valid. In the case at hand, however, every SP tuple must have a counterpart in S, because of the integrity constraint—actually a foreign key constraint—that says that every shipment must have a supplier, and so the transformation is valid after all. A transformation that’s valid only because a certain integrity constraint is in effect is called a semantic transformation, and the resulting optimization is called a semantic optimization.

In principle, any constraint whatsoever can be used in semantic optimization (we’re not limited to foreign key constraints). For example, suppose the suppliers-and-parts database is subject to the constraint “All red parts must be stored in London,” and consider the query:

This is a fairly complex query. But thanks to the integrity constraint, we see that it can be transformed—transformed by the optimizer, I mean, not by the user—into this much simpler one:

We could easily be talking about several orders of magnitude improvement in performance here. And so, while few products do much in the way of semantic optimization at the time of writing (as far as I know), I certainly expect them to do more in the future, because the payoff is so dramatic.

To get back to the main thread of the discussion, I now observe that if a given constraint is to be usable in semantic optimization, that constraint must be satisfied at all times (more precisely, at statement boundaries), not just at transaction boundaries. As we’ve seen, semantic optimization means using constraints to simplify queries in order to improve performance. Clearly, then, if some constraint is violated at some time, then any simplification based on that constraint won’t be valid at that time, and query results based on that simplification will be wrong at that time (in general).

In sum, then, database constraints must be satisfied—that is, they must evaluate to TRUE, given the values currently appearing in the database—at statement boundaries (or, very informally, “at semicolons”); in other words, they must be checked at the end of any statement that might cause them to be violated. If any such check fails, the effects on the database of the offending statement are undone and an exception is raised.

As I’ve said, the conventional wisdom is that multi-relvar constraint checking, at least, has to be deferred to commit time (the arguments of the previous section notwithstanding). By way of example, suppose the suppliers-and-parts database is subject to the following constraint:

  CONSTRAINT C7
    COUNT ( ( S WHERE SNO = SNO('S1') ) { CITY }
        UNION
        ( P WHERE PNO = PNO('P1') ) { CITY } ) < 2 ;

This constraint says that supplier S1 and part P1 must never be in different cities. To elaborate: if relvars S and P contain tuples for supplier S1 and part P1, respectively, then those tuples must contain the same CITY value (if they didn’t, the COUNT invocation would return the value two). However, it’s legal for relvar S to contain no tuple for S1, or relvar P to contain no tuple for P1, or both (in which case the COUNT invocation will return either one or zero). Given our usual sample values, then, each of the following SQL UPDATEs will fail under immediate checking:^[*]

  UPDATE S SET CITY = 'Paris' WHERE SNO = SNO('S1') ;

  UPDATE P SET CITY = 'Paris' WHERE PNO = PNO('P1') ;

Now, the conventional solution to this problem is to defer the checking and to bundle up the two UPDATEs into a transaction, like this:

  BEGIN TRANSACTION ;
  UPDATE S SET CITY = 'Paris' WHERE SNO = SNO('S1') ;
  UPDATE P SET CITY = 'Paris' WHERE PNO = PNO('P1') ;
  COMMIT ;

In this conventional solution, the constraint is checked at commit time, and the database is inconsistent between the two UPDATEs. In particular, if the transaction were to ask the question “Are supplier S1 and part P1 in different cities?” between the two UPDATEs (and if we assume that tuples for S1 and P1 do exist), it would get the answer yes.

  UPDATE S WHERE SNO = SNO('S1') ( CITY := 'Paris' ) ,
  UPDATE P WHERE PNO = PNO('P1') ( CITY := 'Paris' ) ;

  S := ... , P := ... ;

Recall from Chapter 4 that the relvar predicate for a relvar is the intended interpretation—loosely, the meaning—for that relvar. For example, the predicate for relvar S looks like this:

In an ideal world, therefore, this predicate would serve as the criterion for acceptability of updates on relvar S—that is, it would dictate whether a given INSERT or DELETE or UPDATE operation on that relvar can be accepted. But this goal is clearly unachievable:

Thus, the pragmatic “criterion for acceptability of updates,” as opposed to the ideal one, is not the predicate but the corresponding set of constraints, which might thus be regarded as the system’s approximation to the predicate.^[*] Equivalently:

In other words: the system can’t guarantee that the database contains only true propositions—it can guarantee only that it doesn’t contain anything that violates any constraint (meaning it contains no inconsistencies). Sadly, truth and consistency aren’t the same thing. To be specific:

More succinctly, correct implies consistent (but not the other way around), and inconsistent implies incorrect (but not the other way around)—where to say that the database is correct is to say it faithfully reflects the true state of affairs in the real world, no more and no less.

Now let me try to pin down these notions a little more precisely. Let R be a base relvar—I’ll get to views in just a moment—and let C1, C2, . . . , Cn be all of the defined database constraints, single- or multi-relvar, that mention R. Assume for simplicity that each Ci is just a boolean expression (in other words, let’s ignore the constraint names, for simplicity). Then the boolean expression:

  ( C1 ) AND ( C2 ) AND ... AND ( Cn ) AND TRUE

is the total relvar constraint for relvar R (but I’ll refer to it for the purposes of this book as just the constraint for R). Note that final “AND TRUE,” by the way; the implication is that if no constraints are defined for a given relvar, the default is just TRUE.

Now let RC be “the” relvar constraint for relvar R. Clearly, R must never be allowed to have a value that causes RC to evaluate to FALSE. This state of affairs is the motivation for (the first version of) what I like to call The Golden Rule:

Now let V be a view. Then V too has a “total relvar constraint” (which I’ll usually abbreviate to just the constraint for V, for simplicity), derived in an obvious manner from the constraints that apply to the relvars in terms of which V is defined. For example, let SC be the total constraint for base relvar S. Then the total constraint for view LS (“London suppliers”) is:

  ( SC ) AND ( CITY = 'London' )

Now let DB be a database, and let DB contain relvars R1, R2, . . . , Rn (only). Let the constraints for those relvars be RC1, RC2, . . . , RCn, respectively. Then the total database constraint for DB, DBC say—which I’ll refer to for the purposes of this book as just the constraint for DB—is the AND of all of those relvar constraints:

  ( RC1 ) AND ( RC2 ) AND ... AND ( RCn )

And here’s a correspondingly extended (in fact, the final) version of The Golden Rule:

Observe in particular that—in accordance with my position that all integrity checking must be immediate—the rule talks in terms of update operations, not transactions.

Now I can take care of a piece of unfinished business. I’ve said we can never trust the answers we get from an inconsistent database. Here’s the proof. As we know, a database can be regarded as a collection of propositions. Suppose that collection is inconsistent; that is, suppose it implies that both p and NOT p are true, where p is some proposition. Now let q be any arbitrary proposition. Then:

But q was arbitrary! It follows that any proposition whatsoever (even obviously false ones like 1 = 0) can be shown to be true in an inconsistent system.

In this section, I want to address a few integrity-related issues that don’t fit very well in any of the preceding sections.

First, since it’s basically a boolean expression that must evaluate to TRUE, it follows that from a formal perspective a constraint is a proposition. Here for example is constraint C1 from the earlier section "Database Constraints“:

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

Given a particular value for relvar S, the boolean expression:

  IS_EMPTY ( S WHERE STATUS < 1 OR STATUS > 100 )

(which might be thought of, loosely, as the constraint proper) is certainly either true or false, unconditionally, and that’s the definition of what it means to be a proposition (see Chapter 4).

Second, suppose relvar S already contains a tuple that violates constraint C1 when the CONSTRAINT statement just shown is executed. Then that execution must fail, of course. More generally, whenever we try to define a new database constraint, the system must first check to see whether that constraint is satisfied by the database at that time. If it isn’t, the constraint must be rejected; otherwise, it must be accepted and enforced from that point forward.

Third, recall from Chapter 1 that the relational model includes what I called a “generic” integrity rule: namely, the referential integrity rule (I deliberately ignore the entity integrity rule). But it should be clear that the referential integrity rule is different in kind from the constraints we’ve been examining in this chapter. It’s really a metaconstraint, in a sense; it says, for example, that in the specific database containing relvars S, P, and SP, there must be certain specific constraints (foreign key constraints) between SP and S and between SP and P—because if there aren’t, then that database might violate the referential integrity metaconstraint. Likewise, in the specific database containing relvars EMP and DEPT (see Chapter 1), there must be a specific foreign key constraint between EMP and DEPT, because if there isn’t, then again that database might violate the referential integrity metaconstraint.

Fourth, I never mentioned the point explicitly, but I trust it’s obvious that we want constraints to be stated declaratively. Although the SQL standard does include fairly extensive support for declarative constraints, at least some of the major SQL products don’t; instead, they assume you’ll use triggered procedures—also known as just triggers—to enforce integrity. (The standard includes explicit support for triggers, too.) As I pointed out in Chapter 1, however, declarative solutions are always to be preferred over procedural ones, if they’re available. Also, declarative constraints in particular open the door to the possibility of doing semantic optimization, which triggers don’t.

Another issue I didn’t mention previously is the possibility of supporting transition constraints. A transition constraint is a constraint on the legal transitions that variables of some kind (relvars in particular) can make from one value to another. For example, a person’s marital status can change from “never married” to “married” but not the other way around. Here’s an example (“No supplier’s status must ever decrease”):

  CONSTRAINT C8 IS_EMPTY
     ( ( ( S' { SNO, STATUS } RENAME ( STATUS AS STATUS' ) )
       JOIN
       ( S { SNO, STATUS } ) )
     WHERE STATUS' > STATUS ) ;

Explanation: I’m adopting the convention that a primed relvar name such as S’ refers to the indicated relvar as it was prior to the update under consideration. Constraint C8 thus says: “If we join the old value of S and the new one and restrict the result to just those tuples where the old status is greater than the new one, the final result must be empty.” (Since the join is on SNO, any tuple in the join for which the old status is greater than the new one would represent a supplier whose status had decreased.)

Last, I hope you agree from everything we’ve covered in this chapter that constraints are vital—and yet they seem to be very poorly supported in commercial products; indeed, they seem to be underappreciated at best, if not completely misunderstood. The emphasis in the commercial world always seems to be on performance, performance, performance; other objectives, such as ease of use, data independence, and in particular integrity, seem so often to be sacrificed to—or at best to take a back seat to—that overriding goal.^[*] But what’s the point of a system performing well if we can’t be sure the information we’re getting from it is correct? Frankly, I don’t care how fast a system runs if I don’t feel I can trust it to give me the right answers to my queries.

I’ve discussed two basic kinds of constraints, type constraints and database constraints. A type constraint defines the set of values that constitute a given type; in Tutorial D, it’s specified as part of the definition of the type in question, and it’s expressed in terms of a possrep (possible representation) for that type. The possrep itself imposes an a priori constraint on the type (and SQL doesn’t support any type constraints apart from such a priori ones). Type constraints are checked as part of the execution of selector operators. As a digression, I elaborated on the notion of selectors and the related notion of THE_ operators, both of which are intimately related to the possrep notion.

Database constraints constrain the values that can appear in a given database (if they apply to just one relvar, they’re sometimes referred to, informally, as relvar constraints). They’re specified by means of a CONSTRAINT statement in Tutorial D or a CREATE ASSERTION statement in SQL, and they’re supposed to be checked “at semicolons” (though they might not be, in SQL). Checking constraints at semicolons means checking them at the end of any statement that might cause them to be violated—which basically means relational assignments, since relational assignment is fundamentally the only operation that can update the database. I gave a series of arguments for rejecting the conventional wisdom that multi-relvar constraints, at least, need not be checked until commit time (and in passing I briefly discussed the notion of semantic optimization). I also introduced the important idea of multiple assignment.

Next, I showed that “the” relvar constraint for relvar R might be regarded as the system’s approximation to the relvar predicate for R; in particular, it serves as the criterion for acceptability of updates on R. “The system can’t enforce truth, only consistency.” The Golden Rule says:

I concluded by claiming that integrity constraints are absolutely vital. To me, in fact, they’re what database systems are all about. To say it again: I don’t care how fast your system runs if I can’t trust the answers it gives me.