Dropping a User-defined Index
Go Up to Improving Index Performance
DROP INDEX removes a user-defined index from the database. System-defined indexes, such as those created on columns defined with
PRIMARY KEY, and
FOREIGN KEY constraints cannot be dropped.
To alter an index, first use the
DROP INDEX statement to delete the index, then use the
CREATE INDEX statement to recreate the index (using the same name) with the desired characteristics.
The syntax for
DROP INDEX is:
DROP INDEX name;
The following statement deletes an index:
DROP INDEX MINSALX;
- Note: The following restrictions apply to dropping an index:
- To drop an index, you must be the creator of the index, a SYSDBA user, or a user with operating system root privileges.
- An index in use cannot be dropped until it is no longer in use. If you try to alter or drop an index while transactions are being processed, the results depend on the type of transaction in operation. In a WAIT transaction, the
DROP INDEXoperation waits until the index is not in use. In a NOWAIT transaction, InterBase returns an error.
- If an index was automatically created by the system on a column having a
PRIMARY KEY, or
FOREIGN KEYconstraint, you cannot drop the index. To drop an index on a column defined with those constraints, drop the constraint, the constrained column, or the table. To modify the constraints, use
ALTER TABLE. For more information about
ALTER TABLE, see the Language Reference.