DROP PRIMARY INDEX

Capella Operational

reference

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?

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

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 )?

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

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'

The index type for a primary index must be Global Secondary Index (GSI). The USING GSI keywords are optional and may be omitted.

Example

To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

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 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 airline;
SELECT * FROM system:indexes WHERE name = '#primary';