Specifications

In this case, we don’t need to generate the primary key because ISBNs are generated elsewhere.
We’ve left the other fields NULL because a bookstore might know the ISBN of a book before
they know the title, author, or price. The order_items table demonstrates how to create
multicolumn primary keys:
create table order_items
( orderid int unsigned not null,
isbn char(13) not null,
quantity tinyint unsigned,
primary key (orderid, isbn)
);
We’ve specified the quantity of a particular book as a TINYINT UNSIGNED, which holds an inte-
ger between 0 and 255.
As we mentioned before, multicolumn primary keys need to be specified with a special pri-
mary key clause. This is used here.
Lastly, if you consider the book_reviews table:
create table book_reviews
(
isbn char(13) not null primary key,
review text
);
This uses a new data type, text, which we have not yet discussed. It is used for longer text,
such as an article. There are a few variants on this, which we’ll discuss later in this chapter.
To understand creating tables in more detail, let’s discuss column names and identifiers in gen-
eral, and then the data types we can choose for columns. First though, let’s look at the database
we’ve created.
Looking at the Database with SHOW and DESCRIBE
Log in to the MySQL monitor and use the books database. You can view the tables in the data-
base by typing
mysql> show tables;
MySQL will display a list of all the tables in the database:
+-----------------+
| Tables in books |
+-----------------+
| book_reviews |
| books |
Using MySQL
P
ART II
198
11 7842 CH08 3/6/01 3:38 PM Page 198