Mastering MySQL 4 [Electronic resources] نسخه متنی

ALTER

The ALTER syntax is as follows:

ALTER [IGNORE] TABLE table_name alter_specification [, alter
_specification ...]

The alter_specification syntax can be any of the following:

ADD [COLUMN] create_definition [FIRST | AFTER
field_name ]
ADD [COLUMN] (create_definition, create_definition,...)
ADD INDEX [index_name] (index_field_name,...)
ADD PRIMARY KEY (index_field_name,...)
ADD UNIQUE [index_name] (index_field_name,...)
ADD FULLTEXT [index_name] (index_field_name,...)
ADD [CONSTRAINT symbol] FOREIGN KEY index_name
(index_field_name,...)[reference_definition]
ALTER [COLUMN] field_name {SET DEFAULT literal | DROP DEFAULT}
CHANGE [COLUMN] old_field_name create_
definition [FIRST | AFTER field_name]
MODIFY [COLUMN] create_definition [FIRST | AFTER field_name]
DROP [COLUMN] field_name
DROP PRIMARY KEY
DROP INDEX index_name
DISABLE KEYS
ENABLE KEYS
RENAME [TO] new_table_name
ORDER BY field_name
table_options

ALTER TABLE allows you to change the structure of an existing table. You can ADD columns, CHANGE column names and definitions, MODIFY (non-ANSI Oracle extension) column definitions without changing the name, DROP columns or indexes, RENAME tables, ORDER data, and DISABLE or ENABLE indexes.

A non-ANSI MySQL extension is that ALTER TABLE can contain multiple components (CHANGE, ADD, and so on) in one statement.

You need ALTER, INSERT, and CREATE permission on the table to use ALTER TABLE.

IGNORE (non-ANSI MySQL extension) causes MySQL to delete records that would result in a duplicate primary or unique key. Usually MySQL would simply abort and the ALTER would fail.

FIRST and ADD...AFTER allow you to specify where a field is to be added in the definition.