How to Use Constraints in Multitable SQL Databases
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.
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)
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 ;
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.