DROP PRIMARY INDEX

reference

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

For compatibility with legacy versions of Couchbase Server, you can also use the DROP INDEX or DROP VECTOR INDEX statement to drop a named primary index.

Prerequisites

RBAC Privileges

To execute the DROP PRIMARY INDEX statement, you must have the Query Manage Index privilege granted on the keyspace. For more information about user roles, see Roles.

Syntax

drop-primary-index ::= 'DROP' 'PRIMARY' 'INDEX' ( index-name? ( 'IF' 'EXISTS' )? |
                       'IF' 'EXISTS' index-name ) 'ON' keyspace-ref index-using?

index-name	(Optional) A unique name that identifies the index. If you do not specify a name, the index with the default name of `#primary` is deleted.
keyspace-ref	(Required) Specifies the keyspace where the index is located. See Keyspace Reference.
index-using	(Optional) Specifies the index type. See USING Clause.

IF EXISTS Clause

The optional IF EXISTS clause enables the statement to complete successfully when the specified primary index does not 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 (` `). See the examples on this page.

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’s no longer reported in the system:indexes output.

DROP PRIMARY INDEX ON airline;
SELECT * FROM system:indexes WHERE name = '#primary';