DROP INDEX

Description

Drops an index.

Precautions

Only the index owner, owner of the schema where the index resides, a user who has the INDEX permission on the table where the index resides, or a user who has the DROP ANY INDEX permission can run the DROP INDEX command. When separation of duties is disabled, a system administrator has this permission by default.
For a global temporary table, if a session has initialized a global temporary table object (including creating a global temporary table and inserting data into the global temporary table for the first time), other sessions cannot delete indexes from the table.

Syntax

DROP INDEX  [ IF EXISTS ] 
    index_name [, ...] [ CASCADE | RESTRICT];

DROP INDEX index_name [, ...] [CASCADE | RESTRICT] ON table_name [algorithm_option | lock_option];

Parameters

IF EXISTS
Reports a notice instead of an error if the specified index does not exist.
index_name
Specifies the index name to be deleted.

Value range: an existing index
CASCADE | RESTRICT
- CASCADE: Dropping the index automatically removes dependent objects.
- RESTRICT: The database refuses to drop the index if any objects depend on it. This is the default value.

table_name
Name of the table.
algorithm_option
For details, see CREATE INDEX.
lock_option
For details, see CREATE INDEX.

Examples

-- Create a table.
m_db=# CREATE TABLE test1_index (id INT, name VARCHAR(20));
m_db=# CREATE INDEX idx_test1 on test1_index (id);

-- Delete an index.
m_db=# DROP INDEX IF EXISTS idx_test1 CASCADE;

-- Delete a table.
m_db=# DROP TABLE test1_index;