How to Drop an Index in MySQL using Drop Index and Alter Table Commands
To drop a non-primary key index, use the
DROP INDEX command:
DROP INDEX index_name ON table_name;
The syntax requires the table name to be specified because MySQL allows index names to be reused on multiple tables.
Primary keys in MySQL are always named
PRIMARY (not case sensitive). But because
PRIMARY is a reserved keyword, backticks are required when you use it in the
DROP INDEX command.
--Enclose PRIMARY in backticks to refer to the name, not the reserved word DROP INDEX `PRIMARY` ON table_name;
Alternatively, MySQL also allows to drop indexes using the
ALTER TABLE command:
-- Drop a non-primary key index ALTER TABLE table_name DROP INDEX index_name; -- Drop the primary key. This time we are using the reserved words so no backticks ALTER TABLE table_name DROP PRIMARY KEY;