Specifications

Each of the tables is created by a separate CREATE TABLE statement. You see that we’ve created
each of the tables in the schema with the columns that we designed in the last chapter. You’ll
see that each of the columns has a data type listed after its name. Some of the columns have
other specifiers, too.
What the Other Keywords Mean
NOT NULL means that all the rows in the table must have a value in this attribute. If it isn’t
specified, the field can be blank (NULL).
AUTO_INCREMENT is a special MySQL feature you can use on integer columns. It means if we
leave that field blank when inserting rows into the table, MySQL will automatically generate a
unique identifier value. The value will be one greater than the maximum value in the column
already. You can only have one of these in each table. Columns that specify AUTO_INCREMENT
must be indexed.
PRIMARY KEY after a column name specifies that this column is the primary key for the table.
Entries in this column have to be unique. MySQL will automatically index this column. Notice
that where we’ve used it above with customerid in the customers table we’ve used it with
AUTO_INCREMENT. The automatic index on the primary key takes care of the index required by
AUTO_INCREMENT.
Specifying PRIMARY KEY after a column name can only be used for single column primary
keys. The PRIMARY KEY clause at the end of the order_items statement is an alternative form.
We have used it here because the primary key for this table consists of the two columns
together.
UNSIGNED after an integer type means that it can only have a zero or positive value.
Understanding the Column Types
Let’s take the first table as an example:
create table customers
( customerid int unsigned not null auto_increment primary key,
name char(30) not null,
address char(40) not null,
city char(20) not null
);
When creating any table, you need to make decisions about column types.
With the customers table, we have four columns as specified in our schema. The first one,
customerid, is the primary key, which we’ve specified directly. We’ve decided this will be an
integer (data type
int) and that these IDs should be unsigned. We’ve also taken advantage of
Using MySQL
P
ART II
196
11 7842 CH08 3/6/01 3:38 PM Page 196