On this page 
The DROP INDEX statement removes indexes from tables.
Synopsis
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. To drop a UNIQUE INDEX, you must use CASCADE.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 | Implicit |
+-------+--------------+--------+-----+--------+-----------+---------+----------+
| tbl | primary | true | 1 | id | ASC | false | false |
| tbl | tbl_name_idx | false | 1 | name | ASC | false | false |
| tbl | tbl_name_idx | false | 2 | id | ASC | false | true |
+-------+--------------+--------+-----+--------+-----------+---------+----------+
(3 rows)
> DROP INDEX tbl@tbl_name_idx;
> SHOW INDEX FROM tbl;
+-------+---------+--------+-----+--------+-----------+---------+----------+
| Table | Name | Unique | Seq | Column | Direction | Storing | Implicit |
+-------+---------+--------+-----+--------+-----------+---------+----------+
| tbl | primary | true | 1 | id | ASC | false | false |
+-------+---------+--------+-----+--------+-----------+---------+----------+
(1 row)
Remove an Index and Dependent Objects with CASCADE
Warning:
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.> SHOW INDEX FROM orders;
+--------+---------------------+--------+-----+----------+-----------+---------+----------+
| Table | Name | Unique | Seq | Column | Direction | Storing | Implicit |
+--------+---------------------+--------+-----+----------+-----------+---------+----------+
| orders | primary | true | 1 | id | ASC | false | false |
| orders | orders_customer_idx | false | 1 | customer | ASC | false | false |
| orders | orders_customer_idx | false | 2 | id | ASC | false | true |
+--------+---------------------+--------+-----+----------+-----------+---------+----------+
(3 rows)
> 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 |
+--------+---------+-------------+-----------+---------+
Was this helpful?
