DROP INDEX

Description

Deletes 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, system administrators have this permission by default.

Syntax

1
2

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

Parameters

• IF EXISTS

  Reports a notice instead of an error if the specified index does not exist.

• index_name

  Specifies the name of the index to be deleted.

  Value range: an existing index

• CASCADE | RESTRICT
  • CASCADE: cascadingly deletes the objects that depend on the index.
  • RESTRICT: refuses to delete the index if any objects depend on it. This is the default action.

Examples

-- Create a table.
gaussdb=# CREATE TABLE test1_index (id INT, name VARCHAR(20));

-- Create an index.
gaussdb=# CREATE INDEX idx_test1 (id);

-- Delete the index.
gaussdb=# DROP INDEX IF EXISTS idx_test1 CASCADE;

-- Delete the table.
gaussdb=# DROP TABLE test1_index;

Helpful Links

ALTER INDEX and CREATE INDEX