MySQL in a Nutshell [55]
ALTER [IGNORE] TABLE table
DROP [COLUMN] column
The DROP clause of the ALTER TABLE statement removes a given column from a table and deletes the column’s data. A table must have at least one column, so this statement will fail if used on the only column in a table. Use the DROP TABLE statement to delete a table. If a dropped column is part of an index, the column will be removed automatically from the index definition. If all of the columns of an index are dropped, the index will automatically be dropped.
Here is an example including this clause:
ALTER TABLE clients
DROP COLUMN miscellaneous,
DROP COLUMN comments;
This statement drops two columns and deletes the data they contain without warning. Notice that multiple columns may be dropped by separating each clause by a comma. It’s not possible to combine clauses. That is to say, ...DROP COLUMN (miscellaneous, comments) is not permitted. Once a column has been deleted, you won’t be able to recover its data from MySQL. Instead, you’ll have to restore the table from a backup of your data if you made one.
ALTER TABLE: DROP index clauses
ALTER [IGNORE] TABLE table
DROP INDEX index |
DROP PRIMARY KEY |
DROP FOREIGN KEY foreign_key_symbol
These clauses are used to delete indexes. A standard index is fairly easy to eliminate with the first syntax shown. Here’s an example of its use:
ALTER TABLE clients
DROP INDEX client_index;
The second syntax deletes the primary key index of a table. However, if the primary key is based on a column with an AUTO_INCREMENT type, you may need to change the column definition in the same statement so it is no longer AUTO_INCREMENT before you can drop the primary key. Here is an example in which we fail to change the indexed column first:
ALTER TABLE clients
DROP PRIMARY KEY;
ERROR 1075 (42000): Incorrect table definition;
there can be only one auto column and it must be defined as a key
ALTER TABLE clients
CHANGE client_id client_id INT,
DROP PRIMARY KEY;
The first SQL statement here causes an error in which MySQL complains that if we are going to have a column with AUTO_INCREMENT, it must be a key column. So using the CHANGE clause in the second SQL statement, we change the client_id column from INT AUTO_INCREMENT to just INT. After the AUTO_INCREMENT is removed, the PRIMARY KEY may be dropped. Before version 5.1 of MySQL, if a primary key doesn’t exist, the first UNIQUE key is dropped instead. After version 5.1, an error is returned and no key is dropped.
To delete a foreign key, the third syntax is used. Here is an example that deletes a foreign index:
ALTER TABLE client
DROP FOREIGN KEY '0_34531';
In this example, the name of the index is not the name of any of the columns, but an index that was created by combining two columns and was given its own name. The name was changed by InnoDB automatically. To get a list of indexes for a table, use the SHOW CREATE TABLE statement.
ALTER TABLE: Miscellaneous clauses
ALTER [IGNORE] TABLE table
CONVERT TO CHARACTER SET charset [COLLATE collation] |
[DEFAULT] CHARACTER SET charset [COLLATE collation] |
DISABLE|ENABLE KEYS |
DISCARD|IMPORT TABLESPACE |
ORDER BY column [ASC|DESC][,...] |
RENAME [TO] table
You can use these miscellaneous clauses with the ALTER TABLE statement to change a variety of table properties. They are described here in the order that they are listed in the syntax.
Converting and setting character sets
The first two syntaxes shown may be used to change the character set and collation for a table. When a table is first created with the CREATE TABLE statement, unless a character set or collation is specified, defaults for these traits are used. To see the character set and collation for a particular table, use the SHOW TABLE STATUS statement. To convert the data, use the CONVERT TO CHARACTER SET clause. To set the table’s default without converting the data, use the DEFAULT CHARACTER SET clause with the ALTER TABLE statement. The following example shows how to convert a table’s character set:
SHOW TABLE STATUS LIKE 'clients' \G
***************************