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 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

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

copy
icon/buttons/copy
$ cockroach demo

Remove an index with no dependencies

Suppose you create an index on the name and city columns of the users table:

copy
icon/buttons/copy
> CREATE INDEX ON users (name, city);
copy
icon/buttons/copy
> SHOW INDEXES FROM users;
  table_name |     index_name      | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+---------------------+------------+--------------+-------------+-----------+---------+-----------
  users      | primary             |   false    |            1 | city        | ASC       |  false  |  false
  users      | primary             |   false    |            2 | id          | ASC       |  false  |  false
  users      | users_name_city_idx |    true    |            1 | name        | ASC       |  false  |  false
  users      | users_name_city_idx |    true    |            2 | city        | ASC       |  false  |  false
  users      | users_name_city_idx |    true    |            3 | id          | ASC       |  false  |   true
(5 rows)

You can drop this index with the DROP INDEX statement:

copy
icon/buttons/copy
> DROP INDEX users@users_name_city_idx;
copy
icon/buttons/copy
> SHOW INDEXES FROM users;
  table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+------------+------------+--------------+-------------+-----------+---------+-----------
  users      | primary    |   false    |            1 | city        | ASC       |  false  |  false
  users      | primary    |   false    |            2 | id          | ASC       |  false  |  false
(2 rows)

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.

Suppose you create a UNIQUE constraint on the id and name columns of the users table:

copy
icon/buttons/copy
> ALTER TABLE users ADD CONSTRAINT id_name_unique UNIQUE (id, name);
copy
icon/buttons/copy
> SHOW CONSTRAINTS from users;
  table_name | constraint_name | constraint_type |            details             | validated
-------------+-----------------+-----------------+--------------------------------+------------
  users      | id_name_unique  | UNIQUE          | UNIQUE (id ASC, name ASC)      |   true
  users      | primary         | PRIMARY KEY     | PRIMARY KEY (city ASC, id ASC) |   true
(2 rows)

If no index exists on id and name, CockroachDB automatically creates an index:

copy
icon/buttons/copy
> SHOW INDEXES from users;
  table_name |   index_name   | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+----------------+------------+--------------+-------------+-----------+---------+-----------
  users      | primary        |   false    |            1 | city        | ASC       |  false  |  false
  users      | primary        |   false    |            2 | id          | ASC       |  false  |  false
  users      | id_name_unique |   false    |            1 | id          | ASC       |  false  |  false
  users      | id_name_unique |   false    |            2 | name        | ASC       |  false  |  false
  users      | id_name_unique |   false    |            3 | city        | ASC       |  false  |   true
(5 rows)

The UNIQUE constraint is dependent on the id_name_unique index, so you cannot drop the index with a simple DROP INDEX statement:

copy
icon/buttons/copy
> DROP INDEX id_name_unique;
ERROR: index "id_name_unique" is in use as unique constraint
SQLSTATE: 2BP01
HINT: use CASCADE if you really want to drop it.

To drop an index and its dependent objects, you can use CASCADE:

copy
icon/buttons/copy
> DROP INDEX id_name_unique CASCADE;
copy
icon/buttons/copy
> SHOW INDEXES from users;
  table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
-------------+------------+------------+--------------+-------------+-----------+---------+-----------
  users      | primary    |   false    |            1 | city        | ASC       |  false  |  false
  users      | primary    |   false    |            2 | id          | ASC       |  false  |  false
(2 rows)
copy
icon/buttons/copy
> SHOW CONSTRAINTS from users;
  table_name | constraint_name | constraint_type |            details             | validated
-------------+-----------------+-----------------+--------------------------------+------------
  users      | primary         | PRIMARY KEY     | PRIMARY KEY (city ASC, id ASC) |   true
(1 row)

See Also



YesYes NoNo