DROP INDEX - CockroachDB

The DROP INDEX removes indexes from tables. The DROP INDEX statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Synopsis

Required privileges

The user must have the CREATE 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 .
`index\_name`	The name of the index you want to drop. Find index names with . You cannot drop a table’s primary index.
`CASCADE`	Drop all objects (such as ) that depend on the indexes. `CASCADE` does not list objects it drops, so should be used cautiously. To drop an index created with , you do not need to use `CASCADE`.
`RESTRICT`	(Default) Do not drop the indexes if any objects (such as ) 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 .

Examples

Setup

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

$ cockroach demo

Remove an index with no dependencies

created as part of a statement cannot be removed without using CASCADE. Unique indexes created with do not have this limitation. Suppose you create an index on the name and city columns of the users table:

> CREATE INDEX ON users (name, city);

> SHOW INDEXES FROM users;

  table_name |     index_name      | non_unique | seq_in_index | column_name | direction | storing | implicit | visible
-------------+---------------------+------------+--------------+-------------+-----------+---------+----------+----------
  users      | users_name_city_idx |     t      |            1 | name        | ASC       |    f    |    f     |    t
  users      | users_name_city_idx |     t      |            2 | city        | ASC       |    f    |    f     |    t
  users      | users_name_city_idx |     t      |            3 | id          | ASC       |    f    |    t     |    t
  users      | users_pkey          |     f      |            1 | city        | ASC       |    f    |    f     |    t
  users      | users_pkey          |     f      |            2 | id          | ASC       |    f    |    f     |    t
  users      | users_pkey          |     f      |            3 | name        | N/A       |    t    |    f     |    t
  users      | users_pkey          |     f      |            4 | address     | N/A       |    t    |    f     |    t
  users      | users_pkey          |     f      |            5 | credit_card | N/A       |    t    |    f     |    t
(8 rows)

You can drop this index with the DROP INDEX statement:

> DROP INDEX users@users_name_city_idx;

> SHOW INDEXES FROM users;

  table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible
-------------+------------+------------+--------------+-------------+-----------+---------+----------+----------
  users      | users_pkey |     f      |            1 | city        | ASC       |    f    |    f     |    t
  users      | users_pkey |     f      |            2 | id          | ASC       |    f    |    f     |    t
  users      | users_pkey |     f      |            3 | name        | N/A       |    t    |    f     |    t
  users      | users_pkey |     f      |            4 | address     | N/A       |    t    |    f     |    t
  users      | users_pkey |     f      |            5 | credit_card | N/A       |    t    |    f     |    t
(5 rows)

Remove an index and dependent objects with `CASCADE`

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 constraint on the id and name columns of the users table:

> ALTER TABLE users ADD CONSTRAINT id_name_unique UNIQUE (id, name);

> SHOW CONSTRAINTS from users;

  table_name | constraint_name | constraint_type |            details             | validated
-------------+-----------------+-----------------+--------------------------------+------------
  users      | id_name_unique  | UNIQUE          | UNIQUE (id ASC, name ASC)      |     t
  users      | users_pkey      | PRIMARY KEY     | PRIMARY KEY (city ASC, id ASC) |     t
(2 rows)

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

> SHOW INDEXES from users;

  table_name |   index_name   | non_unique | seq_in_index | column_name | direction | storing | implicit | visible
-------------+----------------+------------+--------------+-------------+-----------+---------+----------+----------
  users      | id_name_unique |     f      |            1 | id          | ASC       |    f    |    f     |    t
  users      | id_name_unique |     f      |            2 | name        | ASC       |    f    |    f     |    t
  users      | id_name_unique |     f      |            3 | city        | ASC       |    t    |    t     |    t
  users      | users_pkey     |     f      |            1 | city        | ASC       |    f    |    f     |    t
  users      | users_pkey     |     f      |            2 | id          | ASC       |    f    |    f     |    t
  users      | users_pkey     |     f      |            3 | name        | N/A       |    t    |    f     |    t
  users      | users_pkey     |     f      |            4 | address     | N/A       |    t    |    f     |    t
  users      | users_pkey     |     f      |            5 | credit_card | N/A       |    t    |    f     |    t
(8 rows)

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

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

> DROP INDEX id_name_unique CASCADE;

> SHOW INDEXES from users;

  table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit | visible
-------------+------------+------------+--------------+-------------+-----------+---------+----------+----------
  users      | users_pkey |     f      |            1 | city        | ASC       |    f    |    f     |    t
  users      | users_pkey |     f      |            2 | id          | ASC       |    f    |    f     |    t
  users      | users_pkey |     f      |            3 | name        | N/A       |    t    |    f     |    t
  users      | users_pkey |     f      |            4 | address     | N/A       |    t    |    f     |    t
  users      | users_pkey |     f      |            5 | credit_card | N/A       |    t    |    f     |    t
(5 rows)

> SHOW CONSTRAINTS from users;

  table_name | constraint_name | constraint_type |            details             | validated
-------------+-----------------+-----------------+--------------------------------+------------
  users      | users_pkey      | PRIMARY KEY     | PRIMARY KEY (city ASC, id ASC) |   true
(1 row)

​Synopsis

​Required privileges

​Parameters

​Viewing schema changes

​Examples

​Setup

​Remove an index with no dependencies

​Remove an index and dependent objects with CASCADE

​See also