DROP DATABASE

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

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Required privileges

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

Synopsis

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 or if .
CASCADE	(Default) Drop all tables and views in the database as well as all objects (such as and ) 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 or .

Viewing schema changes

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

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 .

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

$ 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 and ) that depend on those tables.

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

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

> DROP DATABASE movr;

ERROR: rejected (sql_safe_updates = true): DROP DATABASE on current database
SQLSTATE: 01000

> USE defaultdb;

> DROP DATABASE movr;

ERROR: rejected (sql_safe_updates = true): DROP DATABASE on non-empty database without explicit CASCADE
SQLSTATE: 01000

> DROP DATABASE movr CASCADE;

> 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:

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

> USE defaultdb;

> DROP DATABASE movr RESTRICT;

ERROR: database "movr" is not empty and RESTRICT was specified
SQLSTATE: 2BP01

See also