Specifications

Note that in ANSI SQL you can make only one alteration per ALTER TABLE statement, but

MySQL allows you to make as many as you like. Each of the alteration clauses can be used to

change different aspects of the table.

The different types of alteration you can make with this statement are shown in Table 9.4.

TABLE 9.4 Possible Changes with the ALTER TABLE Statement

Syntax Description

ADD [COLUMN] column_description Add a new column in the specified

[FIRST | AFTER column ] location (if not specified, the column

goes at the end). Note that column_

descriptions need a name and a

type, just as in a CREATE statement.

ADD [COLUMN] (column_description, Add one or more new columns at the

column_description,...) end of the table.

ADD INDEX [index] (column,...) Add an index to the table on the speci-

fied column or columns.

ADD PRIMARY KEY (column,...) Make the specified column or columns

the primary key of the table.

ADD UNIQUE [index] (column,...) Add a unique index to the table on the

specified column or columns.

ALTER [COLUMN] column {SET DEFAULT Add or remove a default value for a

value | DROP DEFAULT} particular column.

CHANGE [COLUMN] column new_column Change the column called column so

_description that it has the description listed.

Note that this can be used to change

the name of a column because a

column_description includes a name.

MODIFY [COLUMN] column_description Similar to CHANGE. Can be used to

change column types, not names.

DROP [COLUMN] column Delete the named column.

DROP PRIMARY KEY Delete the primary index (but not the

column).

DROP INDEX index Delete the named index.

RENAME[AS] new_table_name Rename a table.

Using MySQL

ART II

224

12 7842 CH09 3/6/01 3:36 PM Page 224