Designate the customer column the primary key for the cus-tomers table

from the column definition. These two methods differ only in that they include the con-straint code in different parts of the CREATE TABLE statement.

Creating Constraints at the Column Level

102

	PRIMARY KEY FOREIGN KEY UNIQUE CHECK NOT NULL

N O T E

FIGURE 4-1	Syntax for creating a column-level constraint

As you see later in this chapter, a NOT NULL constraint can be created only at the column level.

FIGURE 4-2

If you create the constraint at the same time you’re creating a table, you list the con-straint after all the columns are defined. In fact, the main difference in the syntax of a column-level constraint and a table-level constraint is that you provide column names for the table-level constraint at the end of the constraint definition inside parentheses, instead of at the beginning of the constraint definition. You can use the table-level approach to

Chapter 4

on a row level, which means you can’t add or delete the entire row if any column value violates a constraint.

To simplify the examples for different types of constraints, the following sections show how to add constraints to an existing table. The original database creation script to build the JustLee database doesn’t address all the needed constraints, so in the next section, you make the necessary additions. After you have learned how to add constraints by using the ALTER TABLE command, you learn how to include constraints at both the column level and table level during initial table creation by using the CREATE TABLE statement.

U S I N G	T H E	P R I M A R Y	K E Y

FIGURE 4-3

Take a look at an example. The CUSTOMERS table stores a row of data for each customer but doesn’t currently have a PRIMARY KEY constraint. Without a PRIMARY KEY designated, a customer could be added twice mistakenly. Confusion could also result if multiple custo-mers have the same name—an order could be charged or shipped to the wrong customer! The Customer# column is included in the CUSTOMERS table to assist in uniquely identifying each customer. Adding a PRIMARY KEY constraint to the Customer# column ensures that each row added is assigned a value that’s unique. Only one PRIMARY KEY constraint can be defined for each table. To designate the Customer# column as the primary key for the CUS-TOMERS table, issue the ALTER TABLE command shown in Figure 4-4. When the command executes, you get a success message, as shown.

Note the following elements in Figure 4-4: