drop-index.md

title	summary	toc
DROP INDEX	The DROP INDEX statement removes indexes from tables.	false

The DROP INDEX statement removes indexes from tables.

Synopsis

{% include sql/diagrams/drop_index.html %}

Required Privileges

The user must have the CREATE privilege on each specified table.

Parameters

Parameter	Description
IF EXISTS	Drop the named indexes if they exist; if they do not exist, do not return an error.
table_name	The name of the table with the index you want to drop. Find table names with SHOW TABLES.
index_name	The name of the index you want to drop. Find index names with SHOW INDEX. You cannot drop a table's primary index.
CASCADE	Drop all objects (such as constraints) that depend on the indexes. CASCADE does not list objects it drops, so should be used cautiously.
RESTRICT	(Default) Do not drop the indexes if any objects (such as constraints) depend on them.

Examples

Remove an Index (No Dependencies)

> SHOW INDEX FROM tbl;

+-------+------------+--------+-----+--------+-----------+---------+
| Table |    Name    | Unique | Seq | Column | Direction | Storing |
+-------+------------+--------+-----+--------+-----------+---------+
| tbl   | primary    | true   |   1 | id     | ASC       | false   |
| tbl   | index_name | false  |   1 | name   | ASC       | false   |
+-------+------------+--------+-----+--------+-----------+---------+

> DROP INDEX tbl@index_name;

> SHOW INDEX FROM tbl;

+-------+---------+--------+-----+--------+-----------+---------+
| Table |  Name   | Unique | Seq | Column | Direction | Storing |
+-------+---------+--------+-----+--------+-----------+---------+
| tbl   | primary | true   |   1 | id     | ASC       | false   |
+-------+---------+--------+-----+--------+-----------+---------+

Remove an Index and Dependent Objects with CASCADE

{{site.data.alerts.callout_danger}}CASCADE drops all dependent objects without listing them, which can lead to inadvertent and difficult-to-recover losses. To avoid potential harm, we recommend dropping objects individually in most cases.{{site.data.alerts.end}}

> SHOW INDEX FROM orders;

+--------+---------------------+--------+-----+----------+-----------+---------+
| Table  |        Name         | Unique | Seq |  Column  | Direction | Storing |
+--------+---------------------+--------+-----+----------+-----------+---------+
| orders | primary             | true   |   1 | id       | ASC       | false   |
| orders | orders_customer_idx | false  |   1 | customer | ASC       | false   |
+--------+---------------------+--------+-----+----------+-----------+---------+

> DROP INDEX orders@orders_customer_idx;

pq: index "orders_customer_idx" is in use as a foreign key constraint

> SHOW CONSTRAINTS FROM orders;

+--------+---------------------------+-------------+------------+----------------+
| Table  |           Name            |    Type     | Column(s)  |    Details     |
+--------+---------------------------+-------------+------------+----------------+
| orders | fk_customer_ref_customers | FOREIGN KEY | [customer] | customers.[id] |
| orders | primary                   | PRIMARY KEY | [id]       | NULL           |
+--------+---------------------------+-------------+------------+----------------+

> DROP INDEX orders@orders_customer_idx CASCADE;

> SHOW CONSTRAINTS FROM orders;

+--------+---------+-------------+-----------+---------+
| Table  |  Name   |    Type     | Column(s) | Details |
+--------+---------+-------------+-----------+---------+
| orders | primary | PRIMARY KEY | [id]      | NULL    |
+--------+---------+-------------+-----------+---------+