SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements

HP NonStop SQL/MX Reference Manual—540440-003

2-119

DROP INDEX Statement

Considerations for DROP INDEX

Examples of DROP INDEX

The DROP INDEX statement deletes an SQL/MX index. See Database Object Names

on page 6-12.

DROP INDEX is an SQL/MX extension.

Syntax Description of DROP INDEX

index

is the ANSI logical name of the index to drop, of the form:

[[catalog-name.]schema-name.]index

where each part of the name is a valid SQL identifier with a maximum of 128

characters. For more information, see Identifiers on page 6-52.

If you specify RESTRICT and the index is being used by to validate a constraint,

the index is not dropped.

If you specify CASCADE and the index is being used to validate a constraint, the

constraint and the index are dropped.

The default is RESTRICT.

Considerations for DROP INDEX

Authorization and Availability Requirements

To drop an index, you must own the schema that contains the index or be the super ID,

and have access to all partitions of the index and the underlying table.

Indexes That Support Constraints

NonStop SQL/MX uses indexes to implement some constraints. You cannot use DROP

INDEX to drop an index that implements a constraint unless you use the CASCADE

option. Use CASCADE to drop all constraints that use the index, including those that

indirectly use it (that is, any referential constraints that rely on a primary key or unique

constraint that uses the index are also dropped). Alternately, if you use the DROP

CONSTRAINT option in an ALTER TABLE statement, NonStop SQL/MX will drop

indexes that it created to implement that constraint.

DROP INDEX index [RESTRICT | CASCADE]