DROP PRIMARY INDEX

  • concept
    +

    The DROP PRIMARY INDEX statement allows you to drop an unnamed primary index.

    Named primary indexes that are created using CREATE PRIMARY INDEX can only be dropped using the DROP INDEX command.

    Prerequisites

    RBAC Privileges

    User executing the DROP PRIMARY INDEX statement must have the Query Manage Index privilege granted on the keyspace. For more details about user roles, see Roles.

    Syntax

    drop-primary-index ::= 'DROP' 'PRIMARY' 'INDEX' ( 'IF' 'EXISTS' )? 'ON' keyspace-ref
                           index-using?
    Syntax diagram: refer to source code listing
    keyspace-ref

    [Required] Specifies the keyspace where the index is located. Refer to Keyspace Reference below.

    index-using

    [Optional] Specifies the index type. Refer to USING Clause below.

    IF EXISTS Clause

    The optional IF EXISTS clause enables the statement to complete successfully when the specified primary index doesn’t exist. If the primary index does not exist within the specified keyspace, then:

    • If this clause is not present, an error is generated.

    • If this clause is present, the statement does nothing and completes without error.

    Keyspace Reference

    keyspace-ref ::= keyspace-path | keyspace-partial
    Syntax diagram: refer to source code listing

    Specifies the keyspace for the primary index to drop. The keyspace reference may be a keyspace path or a keyspace partial.

    If there is a hyphen (-) inside any part of the keyspace reference, you must wrap that part of the keyspace reference in backticks (` `). Refer to the examples below.

    Keyspace Path

    keyspace-path ::= ( namespace ':' )? bucket ( '.' scope '.' collection )?
    Syntax diagram: refer to source code listing

    If the keyspace is a named collection, or the default collection in the default scope within a bucket, the keyspace reference may be a keyspace path. In this case, the query context should not be set.

    namespace

    (Optional) An identifier that refers to the namespace of the keyspace. Currently, only the default namespace is available. If the namespace name is omitted, the default namespace in the current session is used.

    bucket

    (Required) An identifier that refers to the bucket name of the keyspace.

    scope

    (Optional) An identifier that refers to the scope name of the keyspace. If omitted, the bucket’s default scope is used.

    collection

    (Optional) An identifier that refers to the collection name of the keyspace. If omitted, the default collection in the bucket’s default scope is used.

    For example, default:`travel-sample` indicates the default collection in the default scope in the travel-sample bucket in the default namespace.

    Similarly, default:`travel-sample`.inventory.airline indicates the airline collection in the inventory scope in the travel-sample bucket in the default namespace.

    Keyspace Partial

    keyspace-partial ::= collection
    Syntax diagram: refer to source code listing

    Alternatively, if the keyspace is a named collection, the keyspace reference may be just the collection name with no path. In this case, you must set the query context to indicate the required namespace, bucket, and scope.

    collection

    (Required) An identifier that refers to the collection name of the keyspace.

    For example, airline indicates the airline collection, assuming the query context is set.

    USING Clause

    index-using ::= 'USING' 'GSI'
    Syntax diagram: refer to source code listing

    In Couchbase Server 6.5 and later, the index type for a primary index must be Global Secondary Index (GSI). The USING GSI keywords are optional and may be omitted.

    Example

    Example 1. Drop unnamed primary index

    Create an unnamed primary index on the airline keyspace. Once the index creation statement comes back, query system:indexes for status of the index.

    CREATE PRIMARY INDEX ON `travel-sample`.inventory.airline;
    SELECT * FROM system:indexes WHERE name = '#primary';

    Subsequently, drop the unnamed primary index with the following statement so that it is no longer reported in the system:indexes output.

    DROP PRIMARY INDEX ON `travel-sample`.inventory.airline;
    SELECT * FROM system:indexes WHERE name = '#primary';