DROP INDEX

DROP INDEX — remove an index

Syntax

DROP INDEX [schema.]index_name [IF EXISTS]

Description

DROP INDEX removes an existing index. To execute this command you must be the owner of the index. If the index does not exist and you specify IF EXISTS, NuoDB does not generate an error. Otherwise an error is generated if the index does not exist. Refer to CREATE INDEX for information on how to create indexes.

A UNIQUE INDEX that is referenced by a FOREIGN KEY constraint cannot be dropped. The FOREIGN KEY constraint on the referencing table must first be dropped. See ALTER TABLE for details on how to drop a FOREIGN KEY constraint.

Parameters

IF EXISTS: If the index does not exist and you specify IF EXISTS, NuoDB does not generate an error. Otherwise, if the index does not exist, an error is generated.
schema: Optional. The name of the schema that owns the index to be dropped. If schema is not provided, the index must be owned by the current schema.
index_name: Name of an index to remove. If the index is a PRIMARY KEY index, the index_name is TableName..PRIMARY_KEY and must be enclosed in double quotes. See Example 2.

Examples

Example 1: Create an index in the current schema. Drop an index defined in the current schema.

USE HOCKEY
CREATE INDEX idx_hockey_number ON hockey (number);
SHOW TABLE hockey;

    Tables named HOCKEY

    Found table HOCKEY in schema HOCKEY

        Fields:
            ID bigint
                Nullable: No
                Generator: HOCKEY$IDENTITY_SEQUENCE    Generated Always
            NUMBER integer
            NAME string
            POSITION string
            TEAM string
        Primary Index: HOCKEY..PRIMARY_KEY on field: ID
        Unique Index: PLAYER_IDX on fields: NUMBER, NAME, TEAM
        Secondary Index: IDX_HOCKEY_NUMBER on field: NUMBER

DROP INDEX idx_hockey_number;

Example 2: Drop a PRIMARY KEY index

USE HOCKEY;
DROP INDEX "hockey..primary_key";

SHOW TABLE hockey;

    Tables named HOCKEY

    Found table HOCKEY in schema HOCKEY

        Fields:
            ID bigint
                Nullable: No
                Generator: HOCKEY$IDENTITY_SEQUENCE    Generated Always
            NUMBER integer
            NAME string
            POSITION string
            TEAM string
        Unique Index: PLAYER_IDX on fields: NUMBER, NAME, TEAM