DROP INDEX

Warning:
Cockroach Labs will stop providing Assistance Support for this version on November 12, 2021. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

The DROP INDEX statement removes indexes from tables.

Note:

This statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Synopsis

DROP INDEX CONCURRENTLY IF EXISTS table_name @ index_name CASCADE RESTRICT

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.

To drop an index created with CREATE UNIQUE INDEX, you do not need to use CASCADE.
RESTRICT (Default) Do not drop the indexes if any objects (such as constraints) depend on them.
CONCURRENTLY New in v20.1: Optional, no-op syntax for PostgreSQL compatibility. All indexes are dropped concurrently in CockroachDB.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Examples

Remove an index (no dependencies)

icon/buttons/copy
> SHOW INDEX FROM tl;
+------------+-------------+------------+--------------+-------------+-----------+---------+----------+
| table_name | index_name  | non_unique | seq_in_index | column_name | direction | storing | implicit |
+------------+-------------+------------+--------------+-------------+-----------+---------+----------+
| t1         | primary     |   false    |            1 | id          | ASC       |  false  |  false   |
| t1         | t1_name_idx |    true    |            1 | name        | ASC       |  false  |  false   |
| t1         | t1_name_idx |    true    |            2 | id          | ASC       |  false  |   true   |
+------------+-------------+------------+--------------+-------------+-----------+---------+----------+
(3 rows)
icon/buttons/copy
> DROP INDEX t1@t1_name_idx;
icon/buttons/copy
> SHOW INDEX FROM tbl;
+------------+------------+------------+--------------+-------------+-----------+---------+----------+
| table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit |
+------------+------------+------------+--------------+-------------+-----------+---------+----------+
| t1         | primary    |   false    |            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.
icon/buttons/copy
> SHOW INDEX FROM orders;
+------------+---------------------------------------------+------------+--------------+-------------+-----------+---------+----------+
| table_name |                 index_name                  | non_unique | seq_in_index | column_name | direction | storing | implicit |
+------------+---------------------------------------------+------------+--------------+-------------+-----------+---------+----------+
| orders     | primary                                     |   false    |            1 | id          | ASC       |  false  |  false   |
| orders     | orders_auto_index_fk_customer_ref_customers |    true    |            1 | customer    | ASC       |  false  |  false   |
| orders     | orders_auto_index_fk_customer_ref_customers |    true    |            2 | id          | ASC       |  false  |   true   |
+------------+---------------------------------------------+------------+--------------+-------------+-----------+---------+----------+
(3 rows)
icon/buttons/copy
> DROP INDEX orders_auto_index_fk_customer_ref_customers;
pq: index "orders_auto_index_fk_customer_ref_customers" is in use as a foreign key constraint
icon/buttons/copy
> SHOW CONSTRAINTS FROM orders;
+------------+---------------------------+-----------------+--------------------------------------------------+-----------+
| table_name |      constraint_name      | constraint_type |                     details                      | validated |
+------------+---------------------------+-----------------+--------------------------------------------------+-----------+
| orders     | fk_customer_ref_customers | FOREIGN KEY     | FOREIGN KEY (customer) REFERENCES customers (id) |   true    |
| orders     | primary                   | PRIMARY KEY     | PRIMARY KEY (id ASC)                             |   true    |
+------------+---------------------------+-----------------+--------------------------------------------------+-----------+
(2 rows)
icon/buttons/copy
> DROP INDEX orders_auto_index_fk_customer_ref_customers CASCADE;
icon/buttons/copy
> SHOW CONSTRAINTS FROM orders;
+------------+-----------------+-----------------+----------------------+-----------+
| table_name | constraint_name | constraint_type |       details        | validated |
+------------+-----------------+-----------------+----------------------+-----------+
| orders     | primary         | PRIMARY KEY     | PRIMARY KEY (id ASC) |   true    |
+------------+-----------------+-----------------+----------------------+-----------+
(1 row)

See Also

YesYes NoNo