DROP INDEX

The DROP INDEX data description statement deletes the definition of an index from the dictionary. It is also a CA IDMS extension of the SQL standard.

Authorization

To issue a DROP INDEX statement, you must own or have the ALTER privilege on the table on which the index is defined.

Syntax

►►─── DROP INDEX index-name ──────────────────────────────────────────────────►

 ►─── FROM ─┬────────────────┬─ table-identifier ─────────────────────────────►◄
            └─ schema-name. ─┘

Parameters

index-name

Specifies the name of the index being dropped. Index-name must identify an index defined in the dictionary.

FROM table-identifier

Identifies the table on which the named index is defined.

schema-name

Identifies the schema associated with the named table.

If you do not specify a schema-name, the default value is:

The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
The schema associated with the access module used at runtime, if the statement is embedded in an application program

Usage

Indexes on Tables in the SYSTEM Schema

You cannot delete the definition of an index on a table in the SYSTEM schema.

Indexes Used in the Implementation of Referential Constraints

You cannot drop an index that is used in the implementation of a referential constraint if no other existing index or CALC key can be used in place of the index.

Example

Dropping an Index

The following DROP INDEX statement deletes the definition of the BUDGET_DATE_INDEX index from the dictionary:

drop index budget_date_index
   from sales.monthly_budget;

More Information

For more information about defining indexes, see CREATE INDEX.
For more information about unlinked referential constraints, see CREATE CONSTRAINT.