ALTER TABLE syntax

ALTER [IGNORE] TABLE table_name alter_spec [, alter_spec ...]

alter_specification:
	ADD [COLUMN] create_definition [AFTER column_namn | FIRST]
or	CHANGE [COLUMN] old_column_name create_definition
or	ALTER [COLUMN] column_name { SET DEFAULT literal | DROP DEFAULT }
or	ADD INDEX [index_name] ( index_column_name,...)
or	ADD UNIQUE [index_name] ( index_column_name,...)
or	DROP [COLUMN] column_name
or	DROP PRIMARY KEY
or	DROP INDEX key_name
or	RENAME [AS] new_table_name

• ALTER TABLE works by creating a temporary table and copying all information to it and then the old table is deleted and the new one is renamed. This is done in such a way that all updates are automatically redirect to the new table without any failed updates. While the ALTER TABLE is working, the old table is readable for other clients. Table updates/writes to the table are stalled and only executed after the new table is ready.
• If IGNORE isn't specified then the copy will be aborted and rolled back if there exists any duplicated unique keys in the new table. In case of duplicates the first found row will be used. This is a MySQL extension.
• The CHANGE column_name, DROP column_name and DROP INDEX are MySQL extensions to ANSI SQL92.
• The optional word COLUMN is a pure noise word and can be omitted.
• The ADD and CHANGE takes the same create_definition as CREATE TABLE. See section CREATE TABLE syntax..
• In MySQL 3.22 you can use ADD ... AFTER column_namn or FIRST to add a column at some specific location in your table. The default is the add the column last.
• ALTER COLUMN sets a new default value or removes the old default value for a column.
• DROP INDEX removes an index. This is a MySQL extension.
• The FOREIGN KEY syntax in MySQL exists only for compatibility. See section What functionality is missing in MySQL.
• If one drops a column_name which is part of some index, this index part is removed. If all index parts are removed then the index is removed.
• DROP PRIMARY KEY drops index named PRIMARY or if no such index exists, it drops the first UNIQUE index in the table.
• CHANGE tries to convert data to the new format as good as possible.
• With mysql_info(MYSQL*) one can retrieve how many records were copied and how many records were deleted because of multiple indexes.
• To use ALTER TABLE one needs select, insert, delete, update, create and drop privileges on the table.
• If one uses ALTER TABLE table_name RENAME AS new_name without any other options, MySQL will only do a fast rename of table table.

Some examples of using ALTER TABLE:

CREATE TABLE t1 (a INTEGER,b CHAR(10));
INSERT INTO t1 VALUES(1,"testing");
ALTER TABLE t1 RENAME t2;
ALTER TABLE t2 CHANGE a a TINYINT NOT NULL, CHANGE b c CHAR(20);
ALTER TABLE t2 ADD d TIMESTAMP;
ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
ALTER TABLE t2 DROP COLUMN c;
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);
DROP TABLE t2;

Go to the first, previous, next, last section, table of contents.