> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# DROP INDEX
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `DROP INDEX` statement removes indexes from tables.
The \`\` 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` | 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
To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```
### Remove an index with no dependencies
`UNIQUE` indexes created as part of a `CREATE TABLE` statement cannot be removed without using [`CASCADE`](drop-index.html#remove-an-index-and-dependent-objects-with-cascade). Unique indexes created with `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` 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
* Online Schema Changes
* `SHOW JOBS`