The DROP TABLE statement removes a table and all its indexes from a database.

Note:

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 specified table(s). If CASCADE is used, the user must have the privileges required to drop each dependent object as well.

Synopsis

DROP TABLE IF EXISTS table_name , CASCADE RESTRICT

Parameters

Parameter Description
IF EXISTS Drop the table if it exists; if it does not exist, do not return an error.
table_name A comma-separated list of table names. To find table names, use SHOW TABLES.
CASCADE Drop all objects (such as constraints and views) that depend on the table.

CASCADE does not list objects it drops, so should be used cautiously.
RESTRICT (Default) Do not drop the table if any objects (such as constraints and views) depend on it.

Viewing schema changes

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

Examples

Remove a table (no dependencies)

In this example, other objects do not depend on the table being dropped.

copy
icon/buttons/copy
> SHOW TABLES FROM bank;
+--------------------+
| table_name         |
+--------------------+
| accounts           |
| branches           |
| user_accounts_view |
+--------------------+
(3 rows)
copy
icon/buttons/copy
> DROP TABLE bank.branches;
DROP TABLE
copy
icon/buttons/copy
> SHOW TABLES FROM bank;
+--------------------+
| table_name         |
+--------------------+
| accounts           |
| user_accounts_view |
+--------------------+
(2 rows)

Remove a table and dependent objects with CASCADE

In this example, a view depends on the table being dropped. Therefore, it's only possible to drop the table while simultaneously dropping the dependent view using CASCADE.

Warning:
CASCADE drops all dependent objects without listing them, which can lead to inadvertent and difficult-to-recover losses. To avoid potential harm, we recommend dropping objects individually in most cases.
copy
icon/buttons/copy
> SHOW TABLES FROM bank;
+--------------------+
| table_name         |
+--------------------+
| accounts           |
| user_accounts_view |
+--------------------+
(2 rows)
copy
icon/buttons/copy
> DROP TABLE bank.accounts;
pq: cannot drop table "accounts" because view "user_accounts_view" depends on it
copy
icon/buttons/copy
> DROP TABLE bank.accounts CASCADE;
DROP TABLE
copy
icon/buttons/copy
> SHOW TABLES FROM bank;
+------------+
| table_name |
+------------+
+------------+
(0 rows)

See also



Yes No