Informix Guide to SQL: Syntax SQL Statements

Use the DROP INDEX statement to remove an index.

You must be the owner of the index or have the DBA privilege to use the DROP INDEX statement.

The following example drops the index o_num_ix that joed owns. The stores_demo database must be the current database.

DROP  INDEX  stores_demo:joed.o_num_ix 

You cannot use the DROP INDEX statement on a column or columns to drop a unique constraint that is created with a CREATE TABLE statement; you must use the ALTER TABLE statement to remove indexes that are created as constraints with a CREATE TABLE or ALTER TABLE statement.

The index is not actually dropped if it is shared by constraints. Instead, it is renamed in the sysindexes system catalog table with the following format:

[space]<tabid>_<constraint id>

In this example, tabid and constraint_id are from the systables and sysconstraints system catalog tables, respectively. The idxname (index name) column in the sysconstraints table is then updated to reflect this change. For example, the renamed index name might be something like: "121_13" (quotes used to show the spaces).

If this index is a unique index with only referential constraints sharing it, the index is downgraded to a duplicate index after it is renamed.

Related statements: ALTER TABLE, CREATE INDEX, and CREATE TABLE

For information on the performance characteristics of indexes, see your Performance Guide.