SQL and Relational Theory, 2nd Edition

DATABASE CONSTRAINTS

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 some logically equivalent shorthand); in SQL, they’re specified by means of a CREATE ASSERTION statement (or, again, some equivalent shorthand). I don’t want to get into details of those shorthands—at least, not yet—because they’re essentially just a matter of syntax; for now, let me stay with the “longhand” forms. Here then are some examples (Tutorial D on the left and SQL on the right as usual):

Example 1:

Constraint CX1 says: Supplier status values must be in the range 1 to 100 inclusive. This constraint involves just a single attribute of a single relvar. Note in particular that it can be checked for a given supplier tuple by examining just that tuple in isolation—there’s no need to look at any other tuples in the relvar or any other relvars in the database. For that reason, such constraints are sometimes referred to, informally, as tuple constraints, or row constraints in SQL—though this latter term is also used in SQL to mean, more specifically, a row constraint that can’t be formulated as a column constraint (see the section DATABASE CONSTRAINTS IN SQL). Now, all such usages ought really to be deprecated, because constraints constrain updates, and as we saw in Chapter 5 there’s no such thing ...