■ The MySQL monitor
prompts for another line.
› Type url VARCHAR(255),
and press Enter.
ˇ Type description TEXT );
and press Enter.
Note: If you want to add additional
fields, include them before the
closing parenthesis.
■ The table is now created.
Á To verify that the
command worked, type
SHOW COLUMNS FROM links;
and press Enter.
■ The columns you created
for this table are displayed.
If you are not sure what length to use for a CHAR or VARCHAR column,
specify a value large enough to store a typical value. You can always
change the length later. You can also change VARCHAR columns to
TEXT columns without losing data. This is explained in Chapter 3.
The MySQL server may automatically change the type of some of your
columns if there is a conflict between them. In particular, if you use
fixed-length CHAR columns and at least one variable-length VARCHAR
or TEXT column, the entire row must be variable length, so your CHAR
columns are converted to VARCHAR.
In addition, the server automatically changes VARCHAR columns with
a length of one to three characters to CHAR columns, because it is not
practical to use variable length when the column is this small.
While it is important to choose the correct column types when you
create a table, after you have created it, you can work with VARCHAR
and CHAR columns in exactly the same ways. TEXT columns also work

in most of the same ways, except that you cannot use the entire body
of a TEXT field as an index.
47
MANAGE DATABASES AND TABLES
2
516922 Ch02.F 9/26/02 11:32 AM Page 47
⁄ From the MySQL monitor,
type
USE testdb; and press
Enter.
■ The database is now
selected.
Note: If you did not create the testdb
database in Chapter 1, you need to
create it first.
¤ Type CREATE TABLE music (
and press Enter.
■ This starts the table
definition.
■ The MySQL monitor
prompts for the next line.
‹ Type title VARCHAR(100),
and press Enter.
S
ets and enumerations are different from normal text
columns in that they are limited to a specific list of
values. You can use these columns whenever you
need to assign categories to the items in a table. For
example, suppose you were using a database table to
catalog music recordings. You could use a SET column to

store the category of music that each recording contains:
category SET("rock", "pop", "blues",
"country", "dance")
The one potential disadvantage of a SET column is that
each item can have only one value. Anything that fits more
than one category could only be listed in one. This is where
ENUM columns are useful.
With an ENUM column, each item can be assigned any
number of values from the list you specify when you create
the table. You can look at an ENUM column as a list of flags,
each of which can be assigned or not assigned to an item.
In the music catalog example, an ENUM column would be
ideal to store the media the music is available on:
media ENUM("CD","DVD","LP","Cassette")
Although you could use separate columns to achieve the
same effect, ENUM columns are convenient because they
can be assigned values as a single unit. They are also stored
on the server in a highly efficient way, using only one bit per
item, and are thus especially useful when you are storing
many rows of data in a table.
Because ENUM and SET columns can have a large number
of possible values, you may need to split the column
specification into two or more lines when you create the
table.
USING SETS AND ENUMERATIONS
MySQL
48
USING SETS AND ENUMERATIONS
516922 Ch02.F 9/26/02 11:32 AM Page 48
› Type category SET("rock",

"pop", and press Enter.
ˇ Type "blues", "country",
"dance"), and press Enter.
■ These two lines define a
SET column.
Á Type media
ENUM("CD","DVD",
and press Enter.
‡ Type "LP","Cassette") );
and press Enter.
■ This completes the
definition for the ENUM
column and the CREATE
TABLE command. The table
is now created.
The values for an ENUM column are actually stored as integers. A value
of 1 represents the first possible value, 2 represents the second
possible value, and so on. ENUM columns can have a maximum of
65,535 total values available. ENUM values use one byte of storage if
there are less than 256 possible values and two bytes if there are 256
or more.
The values for a SET column are stored using individual bits. This
means that one byte, or eight bits, of storage is required for every
eight members of the set. A SET column can have a maximum of 64
members, which requires eight bytes of storage.
You can change the definition for a SET or ENUM column using the
ALTER TABLE query in MySQL, described in Chapter 3. However,
changing the definition does not change the values stored for existing
data. If you add a value at the beginning of the list, the numeric values
for the entire list will change. You can safely add values at the end of

the list, but the best strategy is to determine the possible values in
advance and include them when you first create the table.
49
MANAGE DATABASES AND TABLES
2
516922 Ch02.F 9/26/02 11:32 AM Page 49
⁄ From the MySQL monitor,
type
USE testdb; and press
Enter.
■ This selects the testdb
database.
¤ Type CREATE TABLE
MailList ( and press Enter.
■ The MySQL monitor
prompts for the next line.
‹ Type name VARCHAR(80)
PRIMARY KEY, and press Enter.
■ This defines the name field
as the primary key.
A
long with the various columns the table will contain,
you can specify a primary key and one or more
indexed columns. In general, you will want to have at
least a primary key for any table. If there is no primary key,
there may be no way to uniquely identify a single record.
This makes it impossible to edit or delete a single row
without affecting other rows in the table.
For the primary key, you should choose a column that will
have a unique value for each row of the table. For example,

for a table that stores a list of names and mailing addresses,
the name field is usually a good primary key, assuming that
the list is small enough that duplicate names is not a
concern.
You can define the primary key by including the keywords
PRIMARY KEY with one of the column definitions when you
create the table, or with a separate PRIMARY KEY definition
that specifies a column for the key in parentheses. In this
case, you can also use multiple fields as a primary key.
The following CREATE TABLE query creates a MailList table
with columns for the name, address, city, state, and postal
code. The name column is defined as the primary key.
CREATE TABLE MailList (
name VARCHAR(80) PRIMARY KEY,
address VARCHAR(120),
city VARCHAR(50),
state VARCHAR(2),
postal VARCHAR(5) );
CREATE AN INDEXED TABLE
MySQL
50
CREATE AN INDEXED TABLE
516922 Ch02.F 9/26/02 11:32 AM Page 50
› Type address
VARCHAR(120), and
press Enter.
ˇ Type city VARCHAR(50),
and press Enter.
Á Type state VARCHAR(2),
and press Enter.

‡ Type postal VARCHAR(5) );
and press Enter.
■ This completes the query,
and the table is created.
° Type SHOW COLUMNS
FROM MailList; and press
Enter.
■ The columns for the table
you created are displayed.
Along with the primary key, one or more
additional indexes are often useful. You should
only define an index on an additional column if
you frequently need to search for values in that
column. You can add an index with the INDEX
keyword, and optionally specify the UNIQUE
keyword to require a unique value for each row.
The following example shows an expanded
mailing list table with an index on the postal code
field. In this case, the UNIQUE keyword is not used
because multiple records can have the same code.
Example:
CREATE TABLE MailList2 (
name VARCHAR(80) PRIMARY KEY,
address VARCHAR(120),
city VARCHAR(50),
state VARCHAR(2),
postal VARCHAR(5),
INDEX (postal) );
51
MANAGE DATABASES AND TABLES

2
516922 Ch02.F 9/26/02 11:32 AM Page 51
⁄ From the MySQL monitor,
type
USE testdb; and press
Enter.
■ This selects the database.
Note: This task uses the prices table
created earlier in this chapter.
¤ Type DROP TABLE prices;
and press Enter.
■ The table is deleted.
‹ Type SHOW TABLES; and
press Enter.
■ Verify that the deleted
table is not listed.
I
f you no longer have any use for a table, you can delete
it using the DROP TABLE command. This command
immediately deletes the table, including all data. You
can specify more than one table to drop with DROP TABLE,
separated by commas. The following command deletes
the prices table:
DROP TABLE prices;
When you attempt to drop a table that does not exist,
MySQL returns an error. You can optionally specify the IF
EXISTS keywords to prevent the error. The following
example deletes the test1 table only if it exists:
DROP TABLE IF EXISTS test1;
If you want to delete an entire database, you can use the

DROP DATABASE command. This command deletes the
entire database including all tables and data. As with DROP
TABLE, you can use the IF EXISTS keyword to prevent an
error if the database does not exist. The following
command deletes a database called newdb:
DROP DATABASE newdb;
The DROP command does not prompt you for confirmation,
whether you are deleting a table or an entire database.
After you have issued the DROP command, there is no
way to recover the data unless you have a backup copy.
Be sure that you have a backup before you use the DROP
command.
In order to drop a database or table, the MySQL username
you are using must have the correct privileges. Only the
root user is allowed to delete databases by default.
See Chapter 11 for more information about MySQL
security.
In this example you delete the prices table and the newdb
database, which you created in the sections "Create a
Database" and "Create a Simple Table," earlier in this
chapter. Be sure you specify the names correctly to avoid
deleting the wrong data.
DELETE TABLES AND DATABASES
MySQL
52
DELETE A TABLE
516922 Ch02.F 9/26/02 11:32 AM Page 52
The DROP TABLE command deletes the disk files that MySQL uses to
store a table's data. There are typically three separate files for each
table. The DROP DATABASE command deletes the files for each table

in the database and additionally deletes the directory for the database.
If non-MySQL files are also in this directory, it may prevent the
directory from being entirely deleted.
Because the DROP command is drastic, be sure you have a backup of
all data before deleting a table or database. You will learn more about
backing up MySQL databases in Chapter 8.
If you are unsure whether to delete a table, you can use the SHOW
COLUMNS command to display the columns of the table.
Example:
SHOW COLUMNS FROM tablename;
You can also use a SELECT query to display the data in the table before
deleting it. The following command displays all of the rows of a table.
Example:
SELECT * FROM tablename;
53
DELETE A DATABASE
⁄ From the MySQL monitor,
type
DROP DATABASE newdb;
and press Enter.
■ The database is deleted. ¤ Type SHOW DATABASES;
and press Enter.
■ The list of databases is
displayed. Verify that the
database has been deleted.
MANAGE DATABASES AND TABLES
2
516922 Ch02.F 9/26/02 11:32 AM Page 53
W
hen you create a table with the CREATE TABLE

command in MySQL, you specify the column
definitions and other options. If you later decide
to change any aspect of the table's definition, you can do so
using the ALTER TABLE command. This command allows
you to change column names, column types, and other
aspects of a table's definition.
USING ALTER TABLE QUERIES
MySQL
54
Basic ALTER TABLE Syntax
You can make any number of changes with a single
ALTER TABLE command. You can use a variety of
commands within ALTER TABLE to add columns,
remove columns, and make other changes. If you use
multiple keywords to make changes, separate them
with commas.
Example:
ALTER TABLE address
ADD COLUMN lastvisit DATE,
DROP COLUMN postal;
ADD COLUMN
Use the ADD COLUMN command to add a column to the
table. Specify the new column name, the column type,
and any attributes. You can use the same syntax as you
use when creating a table, as described in Chapter 2.
You can optionally specify the keyword FIRST after
ADD COLUMN to add the column at the beginning of the
table, or the AFTER keyword and a column name to
add it after an existing column. If you do not specify
either of these, the column is added at the end of the

table.
DROP COLUMN
The DROP COLUMN command enables you to delete an
existing table column. Use this command with caution,
because it deletes all of the data stored in that column
in existing table rows without asking for confirmation.
To use DROP COLUMN, simply specify the column name.
CHANGE
The CHANGE command changes the definition of an
existing column. To use this command, specify the old
column name followed by the new name, the column
type, and any options such as DEFAULT or NULL.
Specify the old name twice if you are not renaming the
column.
The MySQL server attempts to convert any existing data
in the column to the new column type. However, in
some cases, such as when you change the length of a
VARCHAR column to a smaller amount, the data in the
column will be truncated and cannot be restored.
Example:
ALTER TABLE address
CHANGE name name CHAR(120) NOT NULL;
MODIFY
The MODIFY command changes the definition for a
column without changing its name. To use this command,
specify the column name, the new column type, and any
options. As with CHANGE, the data is converted wherever
possible to the new format.
Example:
ALTER TABLE address

MODIFY name CHAR(120) NOT NULL;
516922 Ch03.F 9/26/02 11:32 AM Page 54
MODIFY TABLES
3
55
ADD INDEX
Use the ADD INDEX command to add an index to the
table for an existing column. To use this command,
specify an optional index name followed by the
column or columns to index in parentheses. If you
use ADD UNIQUE instead of ADD INDEX, a unique
index is created. Before adding a unique index, be
sure the existing rows of the table have unique
values for the column or columns you plan to index.
Example:
ALTER TABLE address
ADD INDEX postindex (postal);
DROP INDEX
The DROP INDEX command deletes an existing index.
To use this command, specify the index name. The index
name is the column name by default, or the name you
specified when creating the index.
Example:
ALTER TABLE address
DROP INDEX postindex;
ADD PRIMARY KEY
The ADD PRIMARY KEY command adds a primary key.
This can only be used if the table does not have an
existing primary key. To use this command, specify
one or more columns to act as the primary key. Each

existing row of the table must have a unique value for
the column or columns specified. The column you
specify must also have the NOT NULL attribute. You
can alter the column to add this attribute if necessary,
as described earlier in this section.
Example:
ALTER TABLE address
ADD PRIMARY KEY (name, address);
DROP PRIMARY KEY
The DROP PRIMARY KEY command removes an
existing primary key. This only removes the indexing
information, not the column or columns that act as the
key. This command does not require any parameters.
Example:
ALTER TABLE address
DROP PRIMARY KEY;
RENAME
The RENAME command renames an existing table. To
use this command, specify the existing table name and
the new name. You can use RENAME TO as a synonym
for RENAME.
Example:
ALTER TABLE temp
RENAME TO temp2;
Table Options
You can change table options using an ALTER TABLE
command. These options include TYPE, the table type,
COMMENT, the optional comment field, and other
options. The complete list of options is in Chapter 2.
To change options, specify each option followed by an

equal sign and the new value.
Example:
ALTER TABLE temp
TYPE=Heap,
COMMENT="I changed the table type.";
ORDER BY
The ORDER BY command sorts the existing data in a
table by the value of a specified column. While you can
retrieve the rows of the table in any order, this command
is useful to make the order permanent when the table is
rarely changed and usually retrieved in a particular order.
Example:
ALTER TABLE address
ORDER BY name;
516922 Ch03.F 9/26/02 11:32 AM Page 55
Note: This example uses the testdb
database and the address table. If
you have not created these, follow
the instructions in Chapter 1 or on
the CD-ROM.
⁄ From the MySQL monitor,
type
USE testdb; and press
Enter.
■ The database is now
selected.
¤ Type SHOW COLUMNS
FROM address; and press
Enter.
■ The current list of columns

is displayed.
I
f you have created a few tables in MySQL, you learned
that there are many decisions to be made — the
columns to include, their types, and other aspects of the
table. Fortunately, you can change most of these after the
table is created with the ALTER TABLE command.
You can use ALTER TABLE ADD COLUMN to add a column
to an existing table. The basic query syntax specifies the
table, the name of the new column, and its column type:
ALTER TABLE address ADD COLUMN
country VARCHAR(10);
You can optionally specify where the new column should
be added. Either specify the keyword FIRST to insert the
new column as the first column in the table's definition, or
specify the AFTER keyword followed by the name of an
existing column. The following example adds a country
column to the address table after the existing state column:
ALTER TABLE address ADD COLUMN
country VARCHAR(10) AFTER state;
When you add a column, you can specify the same
attributes that you use when creating a table, as described
in Chapter 2. You can specify the column's type and display
width, select a default value for the column, and specify
whether null values are allowed.
You cannot specify values for the data in the table's rows
for the new column. The default value of the column will be
used for all rows of the table until you change the data.
Be sure the new column's name does not conflict with
other columns in the table. The column must also be

compatible with the existing columns: In particular, if there
are existing variable-length text columns, such as VARCHAR
or TEXT, you cannot add a fixed-length CHAR column.
ADD A COLUMN TO A TABLE
MySQL
56
ADD A COLUMN TO A TABLE
516922 Ch03.F 9/26/02 11:32 AM Page 56
‹ Type ALTER TABLE address
ADD COLUMN and press
Enter.
■ The MySQL monitor
prompts for the next line.
› Type country
VARCHAR(10) AFTER state;
and press Enter.
■ The column is now added
to the table.
ˇ Type SHOW COLUMNS
FROM address; and press
Enter.
■ The list of columns is
displayed, including the new
country column.
MODIFY TABLES
3
You can add multiple columns within the same ALTER TABLE
command by separating them with commas and enclosing
the entire list of new columns in parentheses. For example, the
following command adds three new columns to the address table:

Example:
ALTER TABLE address ADD COLUMN (
country VARCHAR(10),
unit VARCHAR(5),
notes TEXT );
The parentheses shown in this example are not necessary unless
you are adding more than one column. When you use this form
of the command to add multiple columns, the columns are always
added at the end of the table; you cannot use the AFTER or
FIRST keywords to control the position of the new columns.
You can combine the ADD COLUMN option with other ALTER
QUERY commands, described later in this chapter. To include
multiple commands, separate them with commas.
Although it makes the ALTER TABLE query easier to read, the
word COLUMN is actually optional. If you use ADD by itself, MySQL
assumes you are adding one or more columns.
57
516922 Ch03.F 9/26/02 11:32 AM Page 57
Note: This example uses the testdb
database and the address table. The
instructions for creating these are in
Chapter 1 and on the CD-ROM.
⁄ Type USE testdb; and
press Enter.
■ The database is now
selected.
¤ Type ALTER TABLE address
ADD INDEX
and press Enter.
■ The MySQL monitor

prompts for the next line.
‹ Type stateindex (state); and
press Enter.
■ The index is now created.
› Type SHOW COLUMNS
FROM address; and press
Enter.
■ The list of columns is
displayed. The value NULL in
the Key field shows the new
index on the state column.
A
s you work with a table in MySQL, you may find that
an index on an additional field would improve
performance. You can use the ALTER TABLE
command to add an index to an existing table. To add an
index, you specify the ADD INDEX keywords, an optional
name for the index, and the existing column or columns to
be indexed.
For example, the following ALTER TABLE query adds an
index called stateindex to the address table, as an index for
the existing state column:
ALTER TABLE address ADD INDEX
stateindex (state);
Because the ADD INDEX command adds a simple index
rather than a primary key, it is unaffected by existing
indexes in the table's definition. When you use this
command to add an index, the MySQL server immediately
begins scanning the table and building the index file.
You can optionally specify more than one column for the

index, separated by commas. In this case, the index will be
based on the combination of values in all of the columns
you list. This example adds an index that uses both the
state and country columns:
ALTER TABLE address ADD INDEX
location (state,country);
If you are adding a column to the table with ADD COLUMN,
you can use ADD INDEX within the same ALTER TABLE
command to add an index based on the new column, as
long as you specify the index after the column definition:
ALTER TABLE address
ADD COLUMN custnum INT UNSIGNED,
ADD INDEX (custnum);
Keep in mind that adding an index does not always improve
performance. In fact, extra indexes on fields that are not
frequently used for searching can slow down access to the
table. See Chapter 10 for information on determining
whether an added index will be beneficial.
ADD AN INDEX
MySQL
58
ADD AN INDEX
516922 Ch03.F 9/26/02 11:32 AM Page 58
MODIFY TABLES
MySQL version 3.22 and later supports an alternate command for
adding indexes to tables. You can use the CREATE INDEX command
to add a regular index, a unique index, or a full-text index to an
existing column.
To add a simple index with CREATE INDEX, specify a name for the
new index, the ON keyword, and the table name. This should be

followed by one or more column names in parentheses. The following
command adds an index on the state column of the address table:
CREATE INDEX stateindex ON address (state);
You can optionally specify the keyword UNIQUE after CREATE to make
the new index a unique index, or FULLTEXT to make it a full-text
index. Full-text indexes index the entire text of a VARCHAR or TEXT
column. If you add a regular index on a TEXT column, you must
specify the length of the column to index in parentheses. This example
adds an index on only the first ten characters of the name column:
CREATE INDEX first10 ON address (name(10));
Functionally, CREATE INDEX is equivalent to the ADD INDEX and
ADD UNIQUE commands with ALTER TABLE, described in this
section and the next section. You cannot add a primary key using
CREATE INDEX.
59
Note: This example uses the testdb
database and the address table. The
instructions for creating these are in
Chapter 1 and on the CD-ROM.
⁄ From the MySQL Monitor,
type
USE testdb; and press
Enter.
■ This selects the database.
¤ Type ALTER TABLE address
and press Enter.
‹ Type ADD COLUMN
custnum INT UNSIGNED, and
press Enter.
› Type ADD INDEX

(custnum); and press Enter.
■ This adds the custnum
column and creates an index
on that column.
ADD A NEW INDEXED COLUMN
3
516922 Ch03.F 9/26/02 11:32 AM Page 59
MySQL
Note: This example uses the testdb
database and the address table. To
create them, see Chapter 1 or the
CD-ROM.
⁄ From the MySQL monitor,
type USE testdb; and press
Enter.
■ The database is selected.
¤ Type ALTER TABLE address
and press Enter.
■ The MySQL monitor
prompts for the next line.
‹ Type ADD PRIMARY KEY
(name); and press Enter.
■ The primary key is now
added.
› Type SHOW COLUMNS
FROM address; and press
Enter.
■ The list of columns is
displayed. The value PRI
in the Key column indicates

that the name field is a
primary key.
60
ADD A PRIMARY KEY OR UNIQUE INDEX
Y
ou can also use the ALTER TABLE command in
MySQL to add a unique index or a primary key to an
existing table. Unique indexes require a unique value
for each row of the table. The primary key is a special type
of unique index that is used as the primary identifier for
each row. Each table can have any number of unique
indexes but only one primary key.
To add a primary key, use ADD PRIMARY KEY with ALTER
TABLE. You can only add a primary key if the table does not
have an existing primary key. The following example adds a
primary key on the name field of the address table:
ALTER TABLE address ADD PRIMARY KEY (name);
To add a unique index, you use the ADD UNIQUE keywords
with the ALTER TABLE command. This example adds a
unique index called key1 to the address table, indexing the
address and city columns:
ALTER TABLE address ADD UNIQUE key1
(address, city);
While adding an ordinary index to a table is simple, you
must consider any data in the table when adding a unique
index or primary key. Because these indexes require a
unique value for each row, MySQL will return an error if
there are duplicate values for the column you are indexing.
To successfully add the index, you will need to ensure that
the rows have unique values for the column that you are

making into a key.
If you are adding a column that will act as the new primary
key, you can specify PRIMARY KEY as part of its column
definition. However, this will only work on an empty table,
because existing rows need unique values for the primary
key.
ADD A PRIMARY KEY
516922 Ch03.F 9/26/02 11:32 AM Page 60
Note: This example uses the testdb
database and the address table. To
create them, see Chapter 1 or the
CD-ROM.
⁄ From the MySQL monitor,
type
USE testdb; and press
Enter.
■ This selects the database.
¤ Type ALTER TABLE address
and press Enter.
‹ Type ADD UNIQUE key1
(address, city); and press Enter.
■ The unique index is now
added on the address and
city columns.
61
MODIFY TABLES
3
ADD A UNIQUE INDEX
While you can add any type of index or primary key to a table using
ALTER TABLE, you can also define them when the table is created

with the CREATE TABLE command. This is often the better way to
add an index, because it ensures that you define the index before any
rows are added to the table, and eliminates problems with non-
unique rows.
For example, the following CREATE TABLE statement creates an
addressindex table. This is identical to the address table, but has the
primary key on the name field and the unique index on the address
field already defined.
Example:
CREATE TABLE addressindex (
name VARCHAR(100) PRIMARY KEY,
address VARCHAR(120),
city VARCHAR(50),
state CHAR(2),
UNIQUE key1 (address) );
This example uses the PRIMARY KEY keyword with the name
column to define the primary key. It uses the UNIQUE keyword in the
last line to define a unique index named key1 on the address column.
516922 Ch03.F 9/26/02 11:32 AM Page 61
Note: This example uses the testdb
database and the address table. See
Chapter 1 or the CD-ROM to create
them.
⁄ From the MySQL monitor,
type
USE testdb; and press
Enter.
■ The database is now
selected.
¤ Type ALTER TABLE address

and press Enter.
■ The MySQL monitor
prompts for the next line.
T
imestamp columns are useful for keeping track of
when a row of the table has been updated. Each time
you modify or insert a row, the first timestamp
column in the table's definition is automatically set to the
current date and time.
As with other column types, you can add a timestamp
column to an existing table using the ALTER TABLE
command in MySQL. For example, the following command
adds a timestamp column called updatetime with a display
width of 14 characters to the address table:
ALTER TABLE address
ADD COLUMN updatetime TIMESTAMP(14);
When you add a new timestamp column to an existing
table, it is filled with the default value for each existing row.
Because the default value for the first timestamp column is
the current date and time, all rows will be set to the current
date and time when you add the column.
If you add a second timestamp column to a table, it is not
updated automatically. The default value for the second
timestamp column will be zero. You can set this column to
the current time and date manually when you add a row.
You can use a timestamp column as an index or primary
key. However, if data already exists in the table, you cannot
make a new timestamp column the primary key when you
add it to the table, because all of the rows will have the
current date and time as the value and the rows will be

non-unique. You must assign unique values before adding a
unique index.
ADD A TIMESTAMP COLUMN
MySQL
62
ADD A TIMESTAMP COLUMN
516922 Ch03.F 9/26/02 11:32 AM Page 62
‹ Type ADD COLUMN
updatetime TIMESTAMP(14);
and press Enter.
■ The timestamp column is
now added to the table.
› Type SHOW COLUMNS
FROM address; and press
Enter.
■ The list of columns is
displayed, including the
column you added.
MODIFY TABLES
3
63
DISPLAY WIDTH FORMAT
14 YYYYMMDDHHMMSS
12 YYMMDDHHMMSS
10 YYMMDDHHMM
8 YYYYMMDD
6 YYMMDD
Because MySQL stores years as four digits, you should always
specify all four digits of the year when assigning a value to a
timestamp or date field. If you only specify two digits, MySQL

assumes that values from 70 to 99 are in the 1900's, and values
from 0 to 69 are in the 2000's.
A timestamp column's display width can be up to 14 digits. When
14 digits are used, the column displays the year, month, date,
hour, minute, and second values. For example, a
TIMESTAMP(14) column set to January 3rd, 2005 at midnight
would return the value 20050103000000.
Smaller values for the display width display only a partial date, as
shown in the table below. The display width does not affect the
actual value. Timestamps always store a value down to the second.
516922 Ch03.F 9/26/02 11:32 AM Page 63
Note: This example uses the testdb
database and the links table. The
instructions to create them are in
Chapter 1 and on the CD-ROM.
⁄ From the MySQL monitor,
type
USE testdb; and press
Enter.
■ The database is now
selected.
¤ Type ALTER TABLE links
ADD COLUMN and press
Enter.
■ The MySQL monitor
prompts for the next line.
‹ Type num INTEGER
UNSIGNED NOT NULL and
press Enter.
W

hile some tables have an obvious choice for a
primary key, such as a name column, some tables
have no value that is guaranteed to be unique for
each row. In this case, you can use an auto-increment
column as a key. When you specify the AUTO_INCREMENT
attribute for a column, each row you add is automatically
assigned a new unique value for that column.
As with other column types, you can use ALTER TABLE
to add an auto-increment column to an existing table. For
example, this command adds a new auto-increment column
called num to the links table:
ALTER TABLE links ADD COLUMN
num INTEGER UNSIGNED NOT NULL
AUTO_INCREMENT PRIMARY KEY;
Any column you use as an auto-increment column must
have the NOT NULL attribute. If you insert a NULL value
into an auto-increment column, the next sequence number
for the field is inserted instead. Auto-increment columns
should also have the UNSIGNED attribute because negative
numbers can cause conflicts.
You can use any of MySQL's integer types for an auto-
increment column. However, be sure to use a type that can
store a wide enough range of values to account for the
maximum amount of rows you will be adding to the table.
You can change the column type later if you need a larger
range, as explained in the section "Change a Column Type,"
later in this chapter.
Auto-increment columns are ideal for use as primary keys
or unique indexes, and in fact MySQL requires that any
auto-increment column be defined as a unique index. You

can use only one auto-increment column per table.
ADD AN AUTO-INCREMENT COLUMN
MySQL
64
ADD AN AUTO-INCREMENT COLUMN
516922 Ch03.F 9/26/02 11:32 AM Page 64
› Type AUTO_INCREMENT
PRIMARY KEY;
and press
Enter.
■ The new column is added
to the table.
ˇ Type SHOW COLUMNS
FROM links;
and press Enter.
■ The list of columns is
displayed, including the new
auto-increment column.
MODIFY TABLES
3
When you add an auto-increment column to a table with
existing data, the rows of the table are assigned values for
the column automatically, counting up from one. This
allows you to use a new auto-increment column as a
primary key or unique index, because the values are
guaranteed to be unique.
When you delete a row from a table with an auto-
increment column, the sequence number on that row is
usually not re-used. Thus, you should not assume that
every number in a certain range will be used for one of

the rows of the table — the only thing you are
guaranteed is that every row has a unique value for the
auto-increment column. If you delete all of the rows of
the table, the auto-increment numbers start over at one.
You can specify the first number to be used when
assigning values to an auto-increment column when you
create the table with the AUTO_INCREMENT parameter.
You can also use this when adding a new auto-increment
column to a table.
When you insert a new row in an auto-increment
column, it is assigned the number after the largest
number used by a current row of the table.
65
516922 Ch03.F 9/26/02 11:32 AM Page 65
Note: This example uses the testdb
database and the links table. See
Chapter 1 or the CD-ROM for
information on creating them.
⁄ From the MySQL monitor,
type
USE testdb; and press
Enter.
■ The database is now
selected.
¤ Type DESCRIBE links; and
press Enter.
■ This displays the current
column list for the links table.
Y
ou can use the ALTER TABLE command in MySQL

to change the name of an existing column. To change
a column's name, use the CHANGE keyword. For
example, this command changes the name of the url
column in the links table to link:
ALTER TABLE links
CHANGE url link VARCHAR(255);
To use CHANGE with ALTER TABLE, you specify the current
name of the column, followed by the new name, the
column's type, and any attributes such as DEFAULT or
UNSIGNED. If you do not want to change the type of the
column, specify the same information you used when
creating the column.
If you are unsure of the column's current type and
attributes, you can use the SHOW COLUMNS command to
find out the details of the column, and repeat them with a
new name. The rules for the new column name are the
same as when creating a table, but you cannot use a name
that is already used in the table.
Keep in mind that when you rename a column, any
applications that refer to the column by name will need to
be modified to use the new name. You will also need to
refer to the new name in any further references to the
column.
In MySQL 4.0 and later, you can use the optional keyword
FIRST after the column definition to move the column to
the beginning of the column list for the table, or the AFTER
keyword followed by a column name to move the column
to a new position after the specified column. Changing the
order of columns does not affect existing data or
applications.

If you specify a different column type or attributes with the
CHANGE keyword, the column's definition will be modified.
This is explained in the next section.
RENAME A COLUMN
MySQL
66
RENAME A COLUMN
516922 Ch03.F 9/26/02 11:32 AM Page 66