DROP DATABASE

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

The DROP DATABASE 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

To follow along, run to start a temporary, in-memory cluster with the sample 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

Statements

Syntax

Data Types

Spatial Data

Constraints

Required privileges

Synopsis

Parameters

Viewing schema changes

Examples

Setup

Drop a database and its objects (`CASCADE`)

Prevent dropping a non-empty database (`RESTRICT`)

See also

​Required privileges

​Synopsis

​Parameters

​Viewing schema changes

​Examples

​Setup

​Drop a database and its objects (CASCADE)

​Prevent dropping a non-empty database (RESTRICT)

​See also

Required privileges

Synopsis

Parameters

Viewing schema changes

Examples

Setup

Drop a database and its objects (`CASCADE`)

Prevent dropping a non-empty database (`RESTRICT`)

See also