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
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. |
Examples
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.
> SHOW TABLES FROM db2;
+------------+
| table_name |
+------------+
| t1 |
| v1 |
+------------+
(2 rows)
> DROP DATABASE db2;
> SHOW TABLES FROM db2;
pq: database "db2" does not exist
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.
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 db2;
+------------+
| table_name |
+------------+
| t1 |
| v1 |
+------------+
(2 rows)
> DROP DATABASE db2 RESTRICT;
pq: database "db2" is not empty and CASCADE was not specified