DROP Statements

Capella Columnar

reference

March 23, 2025

+ 12

This topic describes how you use DROP statements to delete Capella Columnar objects.

A DROP statement is the inverse of a CREATE statement.

DROP statements cannot execute while the cluster is in a scaling state. The evaluation of such DDL statements fails. You can reattempt the action after scaling is complete.

Syntax

DropStmnt EBNF

EBNF
Copy
DropStmnt ::= DropDatabase
            | DropScope
            | DropCollection
            | DropSynonym
            | DropIndex
            | DropFunction
            | DropView

See sqlpp:9_udf.adoc#dropfunction and 5a_views.adoc#dropview for information about dropping functions and views. To drop links you use the UI. See sources:delete-entity.adoc#remove-link.

DropStmnt Diagram

DROP Database

When you drop a database, all of the scopes and other database_object entities in that database are also dropped.

You cannot drop a database if any other databases exist that use entities belonging to that database.

DropDatabase Diagram

DropDatabase

DROP Scope

When you drop a scope, the system also drops any user-defined functions in that scope. However, you cannot drop a scope if there are any user-defined functions or views in other scopes that depend on user-defined functions or collections or views within the scope you attempt to drop.

Show DropScope Diagram

DropScope

DROP Collection

You cannot drop a collection if a user-defined function in the same or a different scope uses it.

Show DropCollection Diagram

DropCollection

DROP Synonym

Show DropSynonym Diagram

DropSynonym

DROP Index

Show DropIndex Diagram

DropIndex

Examples

This example drops the remoteCapella.remoteBeer scope, if it already exists. Dropping a scope also drops any collections contained in it and disconnects any remote links associated with those collections.

SQL++
Copy
 DROP SCOPE remoteCapella.remoteBeer IF EXISTS;

Show additional examples

This example removes individual collections and all of the data they contain.

SQL++
Copy
 DROP COLLECTION remoteCapella.remoteBeer.brewBelgium;
 DROP COLLECTION remoteCapella.remoteBeer.brewGermany;
 DROP COLLECTION remoteCapella.remoteBeer.brewUS;

The following example drops the synonym.

SQL++
Copy
 DROP SYNONYM remoteCapella.remoteBeer.h;

The following example drops the indexes.

SQL++
Copy
 DROP INDEX remoteCapella.remoteBeer.brewBelgium.beer_name_idx;
 DROP INDEX remoteCapella.remoteBeer.brewBelgium.brewery_name_idx;
 DROP INDEX remoteCapella.remoteBeer.brewBelgium.brewery_loc_idx;