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.
idmscu19
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.
This article describes the following information:
2
2
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.
Enforcing Indexes
You cannot drop an index that is used as an enforcing index in the implementation of a unique or referential constraint, if no other existing enforcing index can be used in its place.
For more information on enforcing indexes, see the Enforcing Indexes notes in the usage section of CREATE TABLE.
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: