ALTER TABLE - DROP index clauses

These clauses of the MySQL statement, ALTER TABLE are used to delete indexes in a MySQL table.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

ALTER [IGNORE] TABLE table
DROP INDEX index |
DROP PRIMARY KEY |
DROP FOREIGN KEY foreign_key_symbol

Explanation

These clauses of the MySQL statement, ALTER TABLE are used to delete indexes in a MySQL table. A standard index is fairly easy to eliminate with the first syntax shown.

Examples

Here's an example of this MySQL statement using the first and simple syntax:

ALTER TABLE clients 
DROP INDEX client_index;

The second syntax shown above deletes the primary key index of a MySQL 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. Following 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 MySQL 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 of this MySQL statement is used. Here is an example that deletes a foreign index:

ALTER TABLE client
DROP FOREIGN KEY ...;

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. To get a list of indexes for a MySQL table, use the SHOW INDEXES statement.