DROP INDEX

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

DROPINDEXIFEXISTStable_name@index_nameCASCADERESTRICT

Required privileges

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

Parameters

ParameterDescription
IF EXISTSDrop the named indexes if they exist; if they do not exist, do not return an error.
tablenameThe name of the table with the index you want to drop. Find table names with SHOW TABLES.
index_nameThe name of the index you want to drop. Find index names with SHOW INDEX.You cannot drop a table's primary index.
CASCADEDrop 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.

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)

  1. > SHOW INDEX FROM tl;
  1. +------------+-------------+------------+--------------+-------------+-----------+---------+----------+
  2. | table_name | index_name  | non_unique | seq_in_index | column_name | direction | storing | implicit |
  3. +------------+-------------+------------+--------------+-------------+-----------+---------+----------+
  4. | t1         | primary     |   false    |            1 | id          | ASC       |  false  |  false   |
  5. | t1         | t1_name_idx |    true    |            1 | name        | ASC       |  false  |  false   |
  6. | t1         | t1_name_idx |    true    |            2 | id          | ASC       |  false  |   true   |
  7. +------------+-------------+------------+--------------+-------------+-----------+---------+----------+
  8. (3 rows)
  1. > DROP INDEX t1@t1_name_idx;
  1. > SHOW INDEX FROM tbl;
  1. +------------+------------+------------+--------------+-------------+-----------+---------+----------+
  2. | table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit |
  3. +------------+------------+------------+--------------+-------------+-----------+---------+----------+
  4. | t1         | primary    |   false    |            1 | id          | ASC       |  false  |  false   |
  5. +------------+------------+------------+--------------+-------------+-----------+---------+----------+
  6. (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.

  1. > SHOW INDEX FROM orders;
  1. +------------+---------------------------------------------+------------+--------------+-------------+-----------+---------+----------+
  2. | table_name |                 index_name                  | non_unique | seq_in_index | column_name | direction | storing | implicit |
  3. +------------+---------------------------------------------+------------+--------------+-------------+-----------+---------+----------+
  4. | orders     | primary                                     |   false    |            1 | id          | ASC       |  false  |  false   |
  5. | orders     | orders_auto_index_fk_customer_ref_customers |    true    |            1 | customer    | ASC       |  false  |  false   |
  6. | orders     | orders_auto_index_fk_customer_ref_customers |    true    |            2 | id          | ASC       |  false  |   true   |
  7. +------------+---------------------------------------------+------------+--------------+-------------+-----------+---------+----------+
  8. (3 rows)
  1. > DROP INDEX orders_auto_index_fk_customer_ref_customers;
  1. pq: index "orders_auto_index_fk_customer_ref_customers" is in use as a foreign key constraint
  1. > SHOW CONSTRAINTS FROM orders;
  1. +------------+---------------------------+-----------------+--------------------------------------------------+-----------+
  2. | table_name |      constraint_name      | constraint_type |                     details                      | validated |
  3. +------------+---------------------------+-----------------+--------------------------------------------------+-----------+
  4. | orders     | fk_customer_ref_customers | FOREIGN KEY     | FOREIGN KEY (customer) REFERENCES customers (id) |   true    |
  5. | orders     | primary                   | PRIMARY KEY     | PRIMARY KEY (id ASC)                             |   true    |
  6. +------------+---------------------------+-----------------+--------------------------------------------------+-----------+
  7. (2 rows)
  1. > DROP INDEX orders_auto_index_fk_customer_ref_customers CASCADE;
  1. > SHOW CONSTRAINTS FROM orders;
  1. +------------+-----------------+-----------------+----------------------+-----------+
  2. | table_name | constraint_name | constraint_type |       details        | validated |
  3. +------------+-----------------+-----------------+----------------------+-----------+
  4. | orders     | primary         | PRIMARY KEY     | PRIMARY KEY (id ASC) |   true    |
  5. +------------+-----------------+-----------------+----------------------+-----------+
  6. (1 row)

See Also

Was this page helpful?
YesNo