DROP DATABASE

On this page Carat arrow pointing down
Warning:
Cockroach Labs will stop providing Assistance Support for v22.2 on June 5, 2024. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

The DROP DATABASE statement removes a database and all its objects from a CockroachDB cluster.

Note:

The DROP DATABASE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Warning:

DROP DATABASE now uses the declarative schema changer by default. Declarative schema changer statements and legacy schema changer statements operating on the same objects cannot exist within the same transaction. Either split the transaction into multiple transactions, or disable either the sql.defaults.use_declarative_schema_changer cluster setting or the use_declarative_schema_changer session variable.

Required privileges

The user must have the DROP privilege on the database and on all tables in the database.

Synopsis

DROP DATABASE IF EXISTS database_name CASCADE RESTRICT

Parameters

Parameter Description
IF EXISTS Drop the database if it exists; if it does not exist, do not return an error.
name The name of the database you want to drop. You cannot drop a database if it is set as the current database or if sql_safe_updates = true.
CASCADE (Default) Drop all tables and views in the database as well as all objects (such as constraints and views) that depend on those tables.

CASCADE does not list objects it drops, so should be used cautiously.
RESTRICT Do not drop the database if it contains any tables or views.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

icon/buttons/copy
$ cockroach demo

Drop a database and its objects (CASCADE)

For non-interactive sessions (e.g., client applications), DROP DATABASE applies the CASCADE option by default, which drops all tables and views in the database as well as all objects (such as constraints and views) that depend on those tables.

For interactive sessions from the built-in SQL client, either the CASCADE option must be set explicitly or the --unsafe-updates flag must be set when starting the shell.

icon/buttons/copy
> SHOW TABLES FROM movr;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(6 rows)
icon/buttons/copy
> DROP DATABASE movr;
ERROR: rejected (sql_safe_updates = true): DROP DATABASE on current database
SQLSTATE: 01000
icon/buttons/copy
> USE defaultdb;
icon/buttons/copy
> DROP DATABASE movr;
ERROR: rejected (sql_safe_updates = true): DROP DATABASE on non-empty database without explicit CASCADE
SQLSTATE: 01000
icon/buttons/copy
> DROP DATABASE movr CASCADE;
icon/buttons/copy
> SHOW TABLES FROM movr;
ERROR: target database or schema does not exist
SQLSTATE: 3F000

Prevent dropping a non-empty database (RESTRICT)

When a database is not empty, the RESTRICT option prevents the database from being dropped:

icon/buttons/copy
> SHOW TABLES FROM movr;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(6 rows)
icon/buttons/copy
> USE defaultdb;
icon/buttons/copy
> DROP DATABASE movr RESTRICT;
ERROR: database "movr" is not empty and RESTRICT was specified
SQLSTATE: 2BP01

See also


Yes No
On this page

Yes No