DROP INDEX

The DROP INDEX statement deletes the specified index.

Required privilege

No privilege is required for the index owner.

DROP ANY INDEX for another user's index.

SQL syntax

DROP INDEX [Owner.]IndexName [FROM [Owner.]TableName]

Parameters

The DROP INDEX statement has the parameters:

Description

• If you attempt to drop a "busy" index—an index that is in use or that enforces a foreign key—an error results. To drop a foreign key and the index associated with it, use the ALTER TABLE statement.

• If an index is created through a UNIQUE column constraint, it can only be dropped by dropping the constraint with an ALTER TABLE DROP UNIQUE statement. See "CREATE TABLE" for more information about the UNIQUE column constraint.

• If a DROP INDEX operation is or was active in an uncommitted transaction, other transactions doing DML operations that do not access that index are blocked.

• If an index is dropped, any prepared statement that uses the index is prepared again automatically the next time the statement is executed.

• If no table name is specified, the index name must be unique for the specified or implicit owner. The implicit owner, in the absence of a specified table or owner, is the current user running the program.

• If no index owner is specified and a table is specified, the default owner is the table owner.

• If a table is specified and no owner is specified for it, the default table owner is the current user running the program.

• The table and index owners must be the same.

• An index on a temporary table cannot be dropped by a connection if some other connection has an instance of the table that is not empty.

• If the index is used as the unique index for replication, you must drop the replication scheme before you can drop the index.

Examples

Drop index partsorderedindex which is defined on table orderitems using one of the following:

DROP INDEX partsorderedindex
   FROM purchasing.orderitems;

or

DROP INDEX purchasing.partsorderedindex;

See also

CREATE INDEX