How to Use Constraints in Multitable SQL Databases

By Allen G. Taylor

A constraint is an application rule that the DBMS enforces in SQL. After you define a database, you can include constraints (such as NOT NULL) in a table definition. The DBMS makes sure that you can never commit any transaction that violates a constraint.

You have three kinds of constraints:

  • A column constraint imposes a condition on a column in a table.

  • A table constraint puts a specified constraint on an entire table.

  • An assertion is a constraint that can affect more than one table.

Column constraints

An example of a column constraint is shown in the following Data Definition Language (DDL) statement:

CREATE TABLE CLIENT (
 ClientName  CHAR (30)  NOT NULL,
 Address1   CHAR (30),
 Address2   CHAR (30),
 City    CHAR (25),
 State    CHAR (2),
 PostalCode  CHAR (10),
 Phone    CHAR (13),
 Fax    CHAR (13),
 ContactPerson  CHAR (30)
 ) ;

The statement applies the constraint NOT NULL to the ClientName column, specifying that ClientName may not assume a null value. UNIQUE is another constraint that you can apply to a column. This constraint specifies that every value in the column must be unique. The CHECK constraint is particularly useful because it can take any valid expression as an argument. Consider the following example:

CREATE TABLE TESTS (
 TestName   CHAR (30)  NOT NULL,
 StandardCharge NUMERIC (6,2)
  CHECK (StandardCharge >= 0.0
   AND StandardCharge <= 200.0)
 ) ;

VetLab’s standard charge for a test must always be greater than or equal to zero. And none of the standard tests costs more than $200. The CHECK clause refuses to accept any entries that fall outside the range 0 <= StandardCharge <= 200. Another way of stating the same constraint is as follows:

CHECK (StandardCharge BETWEEN 0.0 AND 200.0)

Table constraints

The PRIMARY KEY constraint specifies that the column to which it applies is a primary key. This constraint applies to the entire table and is equivalent to a combination of the NOT NULL and UNIQUE column constraints. You can specify this constraint in a CREATE statement, as shown in the following example:

CREATE TABLE CLIENT (
 ClientName  CHAR (30) PRIMARY KEY,
 Address1   CHAR (30),
 Address2   CHAR (30),
 City    CHAR (25),
 State   CHAR (2),
 PostalCode  CHAR (10),
 Phone   CHAR (13),
 Fax    CHAR (13),
 ContactPerson CHAR (30)
 ) ;

Named constraints can have some additional functionality. Suppose for example, that you want to do a bulk load of several thousand prospective clients into your PROSPECT table. You have a file that contains mostly prospects in the United States, but with a few Canadian prospects sprinkled throughout the file.

Normally, you want to restrict your PROSPECT table to include only U.S. prospects, but you don’t want this bulk load to be interrupted every time it hits one of the Canadian records. (Canadian postal codes include letters and numbers, but U.S. zip codes contain only numbers.) You can choose to not enforce a constraint on PostalCode until the bulk load is complete, and then you can restore constraint enforcement later.

Initially, your PROSPECT table was created with the following CREATE TABLE statement:

CREATE TABLE PROSPECT (
 ClientName  CHAR (30) PRIMARY KEY,
 Address1   CHAR (30),
 Address2   CHAR (30),
 City    CHAR (25),
 State   CHAR (2),
 PostalCode  CHAR (10),
 Phone   CHAR (13),
 Fax    CHAR (13),
 ContactPerson CHAR (30),
 CONSTRAINT Zip CHECK (PostalCode BETWEEN 0 AND 99999)
 ) ;

Before the bulk load, you can turn off the enforcement of the Zip constraint:

ALTER TABLE PROSPECT
 CONSTRAINT Zip NOT ENFORCED;

After the bulk load is complete, you can restore enforcement of the constraint:

ALTER TABLE PROSPECT
 CONSTRAINT Zip ENFORCED;

At this point you can eliminate any rows that do not satisfy the constraint with:

DELETE FROM PROSPECT
 WHERE PostalCode NOT BETWEEN 0 AND 99999 ;

Assertions

An assertion specifies a restriction for more than one table. The following example uses a search condition drawn from two tables to create an assertion:

CREATE TABLE ORDERS (
 OrderNumber   INTEGER   NOT NULL,
 ClientName   CHAR (30),
 TestOrdered   CHAR (30),
 Salesperson   CHAR (30),
 OrderDate   DATE
 ) ;
CREATE TABLE RESULTS (
 ResultNumber  INTEGER  NOT NULL,
 OrderNumber   INTEGER,
 Result    CHAR (50),
 DateOrdered   DATE,
 PrelimFinal   CHAR (1)
 ) ;
CREATE ASSERTION
 CHECK (NOT EXISTS (SELECT * FROM ORDERS, RESULTS
 WHERE ORDERS.OrderNumber = RESULTS.OrderNumber
 AND ORDERS.OrderDate > RESULTS.DateReported)) ;

This assertion ensures that test results aren’t reported before the test is ordered.