On this page
Warning:
As of October 4, 2019, CockroachDB v2.0 is no longer supported. For more details, refer to the Release Support Policy.
The DROP TABLE
statement removes a table and all its indexes from a database.
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
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. |
Examples
Remove a Table (No Dependencies)
In this example, other objects do not depend on the table being dropped.
> SHOW TABLES FROM bank;
+--------------------+
| Table |
+--------------------+
| accounts |
| branches |
| user_accounts_view |
+--------------------+
(3 rows)
> DROP TABLE bank.branches;
DROP TABLE
> SHOW TABLES FROM bank;
+--------------------+
| Table |
+--------------------+
| 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.> SHOW TABLES FROM bank;
+--------------------+
| Table |
+--------------------+
| accounts |
| user_accounts_view |
+--------------------+
(2 rows)
> DROP TABLE bank.accounts;
pq: cannot drop table "accounts" because view "user_accounts_view" depends on it
> DROP TABLE bank.accounts CASCADE;
DROP TABLE
> SHOW TABLES FROM bank;
+-------+
| Table |
+-------+
+-------+
(0 rows)