# DROP TABLE

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

## 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

### 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

### Remove a table (no dependencies)

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

    > SHOW TABLES FROM movr;

              table_name
    +----------------------------+
      promo_codes
      rides
      user_promo_codes
      users
      vehicle_location_histories
      vehicles
    (6 rows)

    > DROP TABLE promo_codes;

    DROP TABLE

    > SHOW TABLES FROM movr;

              table_name
    +----------------------------+
      rides
      user_promo_codes
      users
      vehicle_location_histories
      vehicles
    (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.

    > SHOW TABLES FROM movr;

              table_name
    +----------------------------+
      rides
      user_promo_codes
      users
      vehicle_location_histories
      vehicles
    (5 rows)

    > 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.

    > SHOW CREATE TABLE vehicles;

      table_name |                                       create_statement
    +------------+-----------------------------------------------------------------------------------------------+
      vehicles   | CREATE TABLE 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),
                 |     CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES 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)

    > DROP TABLE users CASCADE;

    DROP TABLE

    > SHOW TABLES FROM movr;

              table_name
    +----------------------------+
      rides
      user_promo_codes
      vehicle_location_histories
      vehicles
    (4 rows)

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

    > SHOW CREATE TABLE vehicles;

      table_name |                                       create_statement
    +------------+-----------------------------------------------------------------------------------------------+
      vehicles   | CREATE TABLE vehicles (
                 |     id UUID NOT NULL,
                 |     city STRING NOT NULL,
                 |     type STRING NULL,
                 |     owner_id UUID NULL,
                 |     creation_time TIMESTAMP NULL,
                 |     status STRING NULL,
                 |     current_location STRING 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

- `ALTER TABLE`
- `CREATE TABLE`
- `INSERT`
- `RENAME TABLE`
- `SHOW COLUMNS`
- `SHOW TABLES`
- `UPDATE`
- `DELETE`
- `DROP INDEX`
- `DROP VIEW`
- `SHOW JOBS`
- Online Schema Changes