The DROP SCHEMA
statement removes a user-defined schema.
The DROP SCHEMA
statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
DROP SCHEMA
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 schema and on all tables in the schema. If the user is the owner of the schema, DROP
privileges are not necessary.
Syntax
Parameters
Parameter | Description |
---|---|
IF EXISTS |
Drop the schema if it exists. If it does not exist, do not return an error. |
schema_name_list |
The schema, or a list of schemas, that you want to drop. To drop a schema in a database other than the current database, specify the name of the database and the name of the schema, separated by a " . " (e.g., DROP SCHEMA IF EXISTS database.schema; ). |
CASCADE |
Drop all tables and views in the schema 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 |
(Default) Do not drop the schema if it contains any tables or views. |
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:
$ cockroach demo
Drop a schema
> CREATE SCHEMA org_one;
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
org_one
pg_catalog
pg_extension
public
(6 rows)
> DROP SCHEMA org_one;
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
pg_catalog
pg_extension
public
(5 rows)
Drop a schema with tables
To drop a schema that contains tables, you need to use the CASCADE
keyword.
> CREATE SCHEMA org_two;
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
org_two
pg_catalog
pg_extension
public
(6 rows)
> CREATE TABLE org_two.users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
city STRING,
name STRING,
address STRING
);
> SHOW TABLES FROM org_two;
schema_name | table_name | type | estimated_row_count
--------------+------------+-------+----------------------
org_two | users | table | 0
(1 row)
> DROP SCHEMA org_two;
ERROR: schema "org_two" is not empty and CASCADE was not specified
SQLSTATE: 2BP01
> DROP SCHEMA org_two CASCADE;
> SHOW SCHEMAS;
schema_name
----------------------
crdb_internal
information_schema
pg_catalog
pg_extension
public
(5 rows)