ALTER

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.