> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# DROP TYPE

The `DROP TYPE` [statement](/docs/stable/sql-statements.md) drops a specified [enumerated data type](/docs/stable/enum.md) from the current database.

> [!NOTE]
>
> The `DROP TYPE` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](/docs/stable/online-schema-changes.md).

## Synopsis

![drop\_type syntax diagram](https://mintcdn.com/cockroachlabs/R40d76ku6ZWAJ59v/images/sql-diagrams/v26.3/drop_type.svg?fit=max\&auto=format\&n=R40d76ku6ZWAJ59v\&q=85\&s=41f016222b51a02ed2696c59fe41961e)

> [!NOTE]
>
> To drop a [domain data type](/docs/stable/domain.md), use the `DROP DOMAIN` statement. `DROP DOMAIN` shares its syntax and behavior with `DROP TYPE`, as shown in the [synopsis](#synopsis). For an example, refer to [Drop a domain](#drop-a-domain).

## Parameters

| Parameter | Description |
| - | - |
| `DOMAIN` | Drop a [domain data type](/docs/stable/domain.md) instead of an enumerated or composite data type. |
| `IF EXISTS` | Drop the type if it exists. If it does not exist, do not return an error. |
| `type_name_list` | A type name or a comma-separated list of type names to drop. |

## Required privileges

The user must be the owner of the type or domain.

## Details

* You cannot drop a type or domain that is in use by a table.
* You cannot drop a type that a [domain data type](/docs/stable/domain.md) is based on. Drop the domain first.
* You can only drop a user-defined type from the database that contains the type.

## Examples

### Drop a single type

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TYPE IF NOT EXISTS status AS ENUM ('open', 'closed', 'inactive');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW ENUMS;
```

```
  schema |  name  |        value
---------+--------+-----------------------
  public | status | open|closed|inactive
(1 row)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE IF NOT EXISTS accounts (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        balance DECIMAL,
        status status
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
-- sqlchecker: ignore
> DROP TYPE status;
```

```
ERROR: cannot drop type "status" because other objects ([bank.public.accounts]) still depend on it
SQLSTATE: 2BP01
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TABLE accounts;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TYPE status;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW ENUMS;
```

```
  schema | name | value
---------+------+--------
(0 rows)
```

### Drop multiple types

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TYPE weekday AS ENUM ('monday', 'tuesday', 'wednesday', 'thursday', 'friday');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TYPE weekend AS ENUM ('sunday', 'saturday');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW ENUMS;
```

```
  schema |  name   |                  value
---------+---------+-------------------------------------------
  public | weekday | monday|tuesday|wednesday|thursday|friday
  public | weekend | sunday|saturday
(2 rows)
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TYPE weekday, weekend;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW ENUMS;
```

```
  schema | name | value
---------+------+--------
(0 rows)
```

### Drop a domain

Create a [domain data type](/docs/stable/domain.md) and a table that uses it:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DOMAIN postal_code AS STRING CHECK (VALUE ~ '^[0-9]{5}$');
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE addresses (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), zip postal_code);
```

A domain that is in use by a table cannot be dropped:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP DOMAIN postal_code;
```

```
ERROR: cannot drop type "postal_code" because other objects ([defaultdb.public.addresses]) still depend on it
SQLSTATE: 2BP01
```

Drop the dependent table, then drop the domain:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP TABLE addresses;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP DOMAIN postal_code;
```

## Known limitations

* Unlike PostgreSQL, a user who owns a schema cannot drop a type or domain in that schema without also being the owner of the type or domain.
* `DROP TYPE ... CASCADE` and `DROP DOMAIN ... CASCADE` are not supported.

## See also

* [`ENUM`](/docs/stable/enum)
* [`DOMAIN`](/docs/stable/domain)
* [Data types](/docs/stable/data-types.md)
* [`CREATE TYPE`](/docs/stable/create-type)
* [`ALTER TYPE`](/docs/stable/alter-type)
* [`SHOW ENUMS`](/docs/stable/show-enums)