MySQL - How to delete index keys from your table

In MySQL, index keys are identifiers assigned to one or more table columns.

There are two ways to delete index keys from your MySQL table:

• For regular index keys, you need to use the ALTER TABLE statement with the DROP INDEX clause.
• For a PRIMARY KEY index, you need to use the ALTER TABLE ... DROP PRIMARY KEY statement.

This tutorial will help you learn how to delete both types of index keys, starting with the regular index.

MySQL delete index keys

For example, suppose you have a table named Friends with the following definition:

CREATE TABLE `Friends` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`email`),
  FULLTEXT KEY `index_firstName` (`firstName`)
) 

Note how the three columns in the table are assigned index keys.

To remove the firstName index key, you can use the ALTER TABLE statement followed by the DROP INDEX {index name} clause

Consider the following example:

ALTER TABLE `Friends` 
  DROP INDEX `index_firstName`;
-- Query OK, 0 rows affected (0.02 sec)

The index at firstName is specifically named in the CREATE TABLE syntax above as index_firstName, so that’s what you need to specify when deleting the index at the firstName column.

But now we have two indices that are not specifically named in the CREATE TABLE syntax: the id and email indices.

The PRIMARY KEY index in the MySQL table has no name. But for other index keys, MySQL will assign the column name as the name of the index assigned to it.

You can look at the name that MySQL assigns to your index using the SHOW CREATE TABLE statement as follows:

mysql> SHOW CREATE TABLE Friends \G

*************************** 1. row ***************************
       Table: Friends
Create Table: CREATE TABLE `Friends` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `firstName` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

From the output above, you can see that the UNIQUE KEY index for the email column is named email, so you can specify it in the DROP INDEX clause.

Here’s an alternative form of DROP INDEX clause using the ON statement:

DROP INDEX `email` ON `Friends`;
-- Query OK, 0 rows affected (0.01 sec)

Now we still have one index to delete: the PRIMARY KEY index attached to the id column.

MySQL delete PRIMARY KEY index

To delete the PRIMARY KEY index from a MySQL table, you need to use the ALTER TABLE statement with the DROP PRIMARY KEY clause.

Here’s an example:

ALTER TABLE Friends
  DROP PRIMARY KEY;

DROP PRIMARY KEY clause doesn’t accept any name because each MySQL table can only have one PRIMARY KEY.

Finally, if the column assigned as the PRIMARY KEY is an AUTO_INCREMENT column, you will receive an ERROR 1075 when running the statement:

mysql> ALTER TABLE Friends
    ->   DROP PRIMARY KEY;

# response:
ERROR 1075 (42000): Incorrect table definition; 
there can be only one auto column and it must be defined as a key

To successfully remove the index, you need to modify the table column and remove the AUTO_INCREMENT modifier from it.

You can use the MODIFY clause after the DROP PRIMARY KEY clause as follows:

ALTER TABLE Friends
  DROP PRIMARY KEY,
  MODIFY id int NOT NULL;

-- Query OK, 0 rows affected (0.03 sec)

Now the PRIMARY KEY index is deleted from the Friends table.

And that’s how you can delete index keys from a MySQL table. Feel free to copy and modify the code above for your project 😉