SQL Series...15

Constraints

Earlier in this article, I talk about constraints as mechanisms for ensuring that the data you enter into a table column falls within the domain of that column. A constraint is an application rule that the DBMS enforces. 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 different kinds of constraints:

A column constraint imposes a condition on a column in a table. A table constraint is a 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 CHARACTER (30) NOT NULL,
Address1 CHARACTER (30),
Address2 CHARACTER (30),
City CHARACTER (25),
State CHARACTER (2),
PostalCode CHARACTER (10),
Phone CHARACTER (13),
Fax CHARACTER (13),
ContactPerson CHARACTER (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 particu-larly useful in that it can take any valid expression as an argument. Consider the following example:

CREATE TABLE TESTS (

TestName CHARACTER (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 is thus a constraint on the entire table and is equivalent to a combination of the NOT NULL and the UNIQUE column con-straints. You can specify this constraint in a CREATE statement, as shown in the following example:

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

Assertions

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

CREATE TABLE ORDERS (
OrderNumber	INTEGER	NOT NULL,
ClientName	CHARACTER (30),
TestOrdered	CHARACTER (30),
Salesperson	CHARACTER (30),
OrderDate	DATE
) ;
CREATE TABLE RESULTS (
ResultNumber	INTEGER	NOT NULL,
OrderNumber	INTEGER,

Result

DateOrdered

PrelimFinal

) ;

CHARACTER(50),

DATE,

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