ALTER TABLE Syntax for MySQL

Posted by admin

May 6, 2008

ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] …

alter_specification:

ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,…)
| ADD INDEX [index_name] [index_type] (index_col_name,…)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,…)
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,…)
| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,…)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,…)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_options

ALTER TABLE allows you to change the structure of an existing 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 the comment for the table and type of the table.
The syntax for many of the allowable alterations is similar to clauses of the CREATE TABLE
statement. See Section 6.2.5, “CREATE TABLE 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 6.2.5.2, “Silent Column Specification Changes.” For
example, if you try to change a VARCHAR column to CHAR, MySQL will still use VARCHAR if the
table contains other variable-length columns.

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.