The DROP DATABASE statement removes a database and all its objects from a CockroachDB cluster.
This statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Required privileges
The user must have the DROP privilege on the database and on all tables in the database.
Synopsis
`CASCADE` does not list objects it drops, so should be used cautiously. `RESTRICT` | Do not drop the database if it contains any [tables](create-table.html) or [views](create-view.html). ## Viewing schema changes This schema change statement is registered as a job. You can view long-running jobs with [`SHOW JOBS`](show-jobs.html). ## 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](movr.html). To follow along, run [`cockroach demo`](cockroach-demo.html) 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 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.
> 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
CREATE DATABASESHOW DATABASESRENAME DATABASESET DATABASESHOW JOBS- Other SQL Statements
- Online Schema Changes
Was this helpful?
