Understanding Constraints

SQL has evolved through many versions to become a very complete and powerful language. Many of these more powerful features are sophisticated database manipulation tools that provide you with data manipulation techniques such as constraints.

Relational tables and referential integrity have both been discussed several times in prior lessons. As I explained in those lessons, relational databases store data broken into multiple tables, each of which stores related data. Keys are used to create references from one table to the other (and thus the term referential integrity).

For relational database designs to work properly, you need a way to ensure that only valid data is inserted into tables. For example, if the Orders table stores order information, and OrderItems stores order details, you want to ensure that any order IDs referenced in OrderItems exist in Orders. Similarly, any customers referred to in Orders must be present in the Customers table.

Although you can perform checks before inserting new rows (do a SELECT on another table to make sure the values are valid and present), it is best to avoid this practice for the following reasons:

• If database integrity rules are enforced at the client level, every client is obliged to enforce those rules—and inevitably some clients won't.

• You must also enforce the rules on UPDATE and DELETE operations.

• Performing client-side checks is a time-consuming process. Having the DBMS do the checks for you is far more efficient.

DBMSs enforce referential integrity by imposing constraints on database tables. Most constraints are defined in table definitions (using the CREATE TABLE or ALTER TABLE as discussed in Lesson 17, "Creating and Manipulating Tables" ).

Primary Keys

I discussed primary keys briefly in Lesson 1, "Understanding SQL." A primary key is a special constraint that is used to ensure that values in a column (or set of columns) are unique and never change, in other words, a column (or columns) in a table whose values uniquely identify each row in the table. Primary key values uniquely identify every row in a table. This facilitates the direct manipulation of and interaction with individual rows. Without primary keys, it would be very difficult to safely UPDATE or DELETE specific rows without affecting any others.

Any column in a table can be established as the primary key, as long as it meets the following conditions:

• No two rows may have the same primary key value.

• Every row must have a primary key value. (Columns must not allow NULL values.)

• The column containing primary key values can never be modified or updated.

• Primary key values can never be reused. (If a row is deleted from the table, its primary key must not be assigned to any new rows.)

One way to define primary keys is at create time, as follows:

CREATE TABLE Vendors
(
    vend_id         CHAR(10)    NOT NULL PRIMARY KEY,
    vend_name     CHAR(50)    NOT NULL,
    vend_address     CHAR(50)    NULL,
    vend_city     CHAR(50)    NULL,
    vend_state     CHAR        NULL,
    vend_zip     CHAR(10)    NULL
);

In this example, the keyword PRIMARY KEY is added to the table definition so that vend_id becomes the primary key.

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id)

In this example, the same column is defined as the primary key, but the CONSTRAINT syntax is used instead. This syntax can be used both in CREATE TABLE and ALTER TABLE statements.

Foreign Keys

A foreign key is a column in a table whose values must be listed in a primary key in another table. Foreign keys are an extremely important part of ensuring referential integrity. To understand foreign keys, let's look at an example.

The Orders table contains a single row for each order entered into the system. Customer information is stored in the Customers table. Orders in Orders are tied to specific rows in the Customers table by customer ID. The customer ID is the primary key in the Customers table—each customer has a unique ID. The order number is the primary key in the Orders table—each order has a unique number.

The values in the customer ID column in the Orders table are not necessarily unique. If a customer has multiple orders, there will be multiple rows with the same customer ID (although each will have a different order number). But at the same time, the only values that are valid within the customer ID column in Orders are the IDs of customers in the Customers table.

That's what a foreign key does. In our example, a foreign key is defined on the Customer ID column in Orders so that the column can only accept values that are in the Customers table's primary key.

Here's one way to define this foreign key:

CREATE TABLE Orders
(
    order_num     INTEGER        NOT NULL PRIMARY KEY,
    order_date     DATETIME    NOT NULL,
    cust_id         CHAR(10)    NOT NULL REFERENCES
Customers(cust_id)
);

Here the table definition uses the REFERENCES keyword to state that any values in cust_id must be in cust_id in the Customers table.

The same thing could have been accomplished using CONSTRAINT syntax in an ALTER TABLE statement:

CONSTRAINT FOREIGN KEY (cust_id) REFERNCES Customers (cust_id)

Unique Constraints

Unique constraints are used to ensure that all data in a column (or set of columns) is unique. They are similar to primary keys, but there are some important distinctions:

• A table can contain multiple unique constraints, but only one primary key is allowed per table.

• Unique constraint columns can contain NULL values.

• Unique constraint columns can be modified or updated.

• Unique constraint column values may never be reused.

• Unlike primary keys, unique constraints cannot be used to define foreign keys.

An example of the use of constraints is an employees table. Every employee has a unique social security number, but you would not want to use it for the primary key because it is too long (in addition to the fact that you might not want that information easily available). Therefore, every employee also has a unique employee ID (a primary key), in addition to his social security number.

Because the employee ID is a primary key, you can be sure that it is unique. You also might want the DBMS to ensure that each social security number is unique, too (to make sure that a typo does not result in the reuse of someone else's number). You can do this by defining a UNIQUE constraint on the social security number column.

The syntax for unique constraints is similar to that for other constraints. Either the UNIQUE keyword is defined in the table definition, or a separate CONSTRAINT is used.

Check Constraints

Check constraints are used to ensure that data in a column (or set of columns) meets a set of criteria that you specify. Common uses of this are

• Checking minimum or maximum values—for example, preventing an order of 0 items (even though 0 is a valid number)

• Specifying ranges—for example, making sure that a ship date is greater than or equal to today's date and not greater than a year from now

• Only allowing specific values—for example, only allowing M or F in a gender field

In other words, datatypes (discussed in Lesson 1) restrict the type of data that can be stored in a column. Check constraints place further restrictions within that datatype.

The following example applies a check constraint to our OrderItems table to ensure that all items have a quantity greater than 0:

CREATE TABLE OrderItems
(
    order_num     INTEGER        NOT NULL,
    order_item     INTEGER        NOT NULL,
    prod_id         CHAR(10)    NOT NULL,
    quantity    INTEGER       NOT NULL    CHECK (quantity > 0),
    item_price     MONEY        NOT NULL
);

To check that a column named gender contains only M or F you can do the following in an ALTER TABLE statement:

CONSTRAINT CHECK (gender LIKE '[MF]')