> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# DROP TABLE
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `DROP TABLE` statement removes a table and all its indexes from a database.
The \`\` 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_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
To follow along, run `cockroach demo` to start a temporary, in-memory cluster with the `movr` sample dataset preloaded:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```
### Remove a table (no dependencies)
In this example, other objects do not depend on the table being dropped.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TABLE promo_codes;
```
```
DROP TABLE
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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`.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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)
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TABLE users CASCADE;
```
```
DROP TABLE
```
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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`.
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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
* `ALTER TABLE`
* `CREATE TABLE`
* `INSERT`
* `ALTER TABLE ... RENAME TO`
* `SHOW COLUMNS`
* `SHOW TABLES`
* `UPDATE`
* `DELETE`
* `DROP INDEX`
* `DROP VIEW`
* `SHOW JOBS`
* Online Schema Changes