Oracle 5.0, Reference Manual

Page: 1034 / 3232

ALTER TABLE
Syntax
1014
| DISCARD TABLESPACE
| IMPORT TABLESPACE
index_col_name :
col_name [( length )] [ASC | DESC]
index_type :
USING {BTREE | HASH}
table_options :
table_option [[,] table_option ] ... (see CREATE TABLE options)
ALTER TABLE
changes the structure of a table. For example, you can add or delete columns, create
or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can
also change characteristics such as the storage engine used for the table or the table comment.
Following the table name, specify the alterations to be made. If none are given,
ALTER TABLE
does
nothing.
The syntax for many of the permissible alterations is similar to clauses of the
CREATE TABLE
statement. See Section 13.1.10, “
CREATE TABLE
Syntax” , for more information.
Some operations may result in warnings if attempted on a table for which the storage engine does not
support the operation. These warnings can be displayed with
SHOW WARNINGS
. See Section 13.7.5.37,
“
SHOW WARNINGS
Syntax” .
If you use
ALTER TABLE
to change a column specification but
DESCRIBE tbl_name
indicates that
your column was not changed, it is possible that MySQL ignored your modification for one of the
reasons described in
Section 13.1.10.3, “Silent Column Specification Changes” .
For information on troubleshooting
ALTER TABLE
, see Section C.5.7.1, “Problems with
ALTER
TABLE
” .
Storage, Performance, and Concurrency Considerations
In most cases,
ALTER TABLE
makes a temporary copy of the original table. MySQL waits for other
operations that are modifying the table, then proceeds. It incorporates the alteration into the copy,
deletes the original table, and renames the new one. While
ALTER TABLE
is executing, the original
table is readable by other sessions. Updates and writes to the table that begin after the
ALTER TABLE
operation begins are stalled until the new table is ready, then are automatically redirected to the new
table without any failed updates. The temporary table is created in the database directory of the new
table. This can differ from the database directory of the original table for
ALTER TABLE
operations that
rename the table to a different database.
If you use
ALTER TABLE tbl_name RENAME TO new_tbl_name
without any other options, MySQL
simply renames any files that correspond to the table
tbl_name
without making a copy. (You can also
use the
RENAME TABLE
statement to rename tables. See Section 13.1.20, “
RENAME TABLE
Syntax” .)
Any privileges granted specifically for the renamed table are not migrated to the new name. They must
be changed manually.
If you use any option to
ALTER TABLE
other than
RENAME
, MySQL always creates a temporary table,
even if the data wouldn't strictly need to be copied (such as when you change the name of a column).
For
MyISAM
tables, you can speed up index re-creation (the slowest part of the alteration process) by
setting the
myisam_sort_buffer_size
[476] system variable to a high value.
• To use
ALTER TABLE
, you need
ALTER
[576] ,
CREATE
[576] , and
INSERT
[577] privileges for
the table. Renaming a table requires
ALTER
[576] and
DROP
[577] on the old table,
ALTER
[576] ,
CREATE
[576] , and
INSERT
[577] on the new table.
•
IGNORE
is a MySQL extension to standard SQL. It controls how
ALTER TABLE
works if there are
duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If
IGNORE
is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If
IGNORE