# 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

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

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

    > DROP INDEX t1@t1_name_idx;

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

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

    > 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

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

    > DROP INDEX orders_auto_index_fk_customer_ref_customers CASCADE;

    > 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

- Indexes
- Online Schema Changes
- `SHOW JOBS`