DROP TABLE

On this page Carat arrow pointing down
Warning:
As of June 5, 2024, CockroachDB v22.2 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.

Note:

The DROP TABLE statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Warning:

DROP TABLE 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 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_list 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_list 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

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:

icon/buttons/copy
$ cockroach demo

Remove a table (no dependencies)

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

icon/buttons/copy
> 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)
icon/buttons/copy
> DROP TABLE promo_codes;
DROP TABLE
icon/buttons/copy
> SHOW TABLES FROM movr;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(5 rows)

Remove a table and dependent objects with CASCADE

In this example, a foreign key from a different table references the table being dropped. Therefore, it's only possible to drop the table while simultaneously dropping the dependent foreign key constraint 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.
icon/buttons/copy
> SHOW TABLES FROM movr;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(5 rows)
icon/buttons/copy
> DROP TABLE users;
pq: "users" is referenced by foreign key from table "vehicles"

To see how users is referenced from vehicles, you can use the SHOW CREATE statement. SHOW CREATE shows how the columns in a table are created, including data types, default values, indexes, and constraints.

icon/buttons/copy
> SHOW CREATE TABLE vehicles;
  table_name |                                         create_statement
-------------+---------------------------------------------------------------------------------------------------
  vehicles   | CREATE TABLE public.vehicles (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     type VARCHAR NULL,
             |     owner_id UUID NULL,
             |     creation_time TIMESTAMP NULL,
             |     status VARCHAR NULL,
             |     current_location VARCHAR NULL,
             |     ext JSONB NULL,
             |     CONSTRAINT vehicles_pkey PRIMARY KEY (city ASC, id ASC),
             |     CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES public.users(city, id),
             |     INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC),
             |     FAMILY "primary" (id, city, type, owner_id, creation_time, status, current_location, ext)
             | )
(1 row)
icon/buttons/copy
> DROP TABLE users CASCADE;
DROP TABLE
icon/buttons/copy
> SHOW TABLES FROM movr;
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(4 rows)

Use a SHOW CREATE TABLE statement to verify that the foreign key constraint has been removed from vehicles.

icon/buttons/copy
> SHOW CREATE TABLE vehicles;
  table_name |                                       create_statement
-------------+------------------------------------------------------------------------------------------------
  vehicles   | CREATE TABLE public.vehicles (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     type VARCHAR NULL,
             |     owner_id UUID NULL,
             |     creation_time TIMESTAMP NULL,
             |     status VARCHAR NULL,
             |     current_location VARCHAR NULL,
             |     ext JSONB NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC),
             |     FAMILY "primary" (id, city, type, owner_id, creation_time, status, current_location, ext)
             | )
(1 row)

See also


Yes No
On this page

Yes No