> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # DROP INDEX The `DROP INDEX` [statement](/docs/stable/sql-statements.md) removes indexes from tables. > [!NOTE] > > The `DROP INDEX` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](/docs/stable/online-schema-changes.md). ## Synopsis ![drop\_index syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/drop_index.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=99e96e60d0ca8b52d7d89162255940ec) ## Required privileges The user must have the `CREATE` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) 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`](/docs/stable/show-tables). | | `index_name` | The name of the index you want to drop. Find index names with [`SHOW INDEX`](/docs/stable/show-index). You cannot drop a table's primary index. | | `CASCADE` | Drop all objects (such as [constraints](/docs/stable/constraints.md)) 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`](/docs/stable/create-index#unique-indexes), you do not need to use `CASCADE`. | | `RESTRICT` | *(Default)* Do not drop the indexes if any objects (such as [constraints](/docs/stable/constraints.md)) 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`](/docs/stable/show-jobs). ## Examples #### Setup To follow along, run [`cockroach demo`](/docs/stable/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](/docs/stable/movr) sample dataset preloaded: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} $ cockroach demo ``` ### Remove an index with no dependencies [`UNIQUE` indexes](/docs/stable/create-index) created as part of a [`CREATE TABLE`](/docs/stable/create-table) statement cannot be removed without using [/docs/stable/drop-index#remove-an-index-and-dependent-objects-with-cascade](/docs/stable/drop-index.md#remove-an-index-and-dependent-objects-with-cascade). Unique indexes created with [`CREATE INDEX`](/docs/stable/create-index) do not have this limitation. Suppose you create an index on the `name` and `city` columns of the `users` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE INDEX ON users (name, city); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > DROP INDEX users@users_name_city_idx; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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 [`UNIQUE`](/docs/stable/unique) constraint on the `id` and `name` columns of the `users` table: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > ALTER TABLE users ADD CONSTRAINT id_name_unique UNIQUE (id, name); ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > DROP INDEX id_name_unique CASCADE; ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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) ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > 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) ``` ## See also * [Indexes](/docs/stable/indexes.md) * [Online Schema Changes](/docs/stable/online-schema-changes.md) * [`SHOW JOBS`](/docs/stable/show-jobs)