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

# ALTER DOMAIN

The `ALTER DOMAIN` [statement](/docs/stable/sql-statements.md) modifies a [domain data type](/docs/stable/domain.md) in the current database.

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

## Synopsis

![alter\_domain syntax diagram](https://mintcdn.com/cockroachlabs/VQ_w3c6MHYdiS7Tn/images/sql-diagrams/common/alter_domain.svg?fit=max\&auto=format\&n=VQ_w3c6MHYdiS7Tn\&q=85\&s=fe54ba4b88da4083cbd46068a0619b6a)

## Parameters

| Parameter | Description |
| - | - |
| `type_name` | The name of the domain. |
| `SET DEFAULT a_expr` | Set the default value for columns that use the domain data type. The expression cannot reference database objects such as tables, sequences, [user-defined functions](/docs/stable/user-defined-functions.md), or user-defined types. |
| `DROP DEFAULT` | Remove the domain's default value. |
| `SET NOT NULL` | Disallow `NULL` values for the domain. Existing data is validated before the constraint is applied. |
| `DROP NOT NULL` | Allow `NULL` values for the domain. |
| `ADD CONSTRAINT name CHECK (a_expr)` | Add a [`CHECK`](/docs/stable/check) constraint to the domain. Use the `VALUE` keyword in the `CHECK` expression to reference the value being tested. You can omit `CONSTRAINT name` to use a generated constraint name. Existing data is validated before the constraint is applied unless `NOT VALID` is specified. |
| `ADD CONSTRAINT name NOT NULL` | Add a `NOT NULL` constraint to the domain. You can omit `CONSTRAINT name` to use a generated constraint name. Existing data is validated before the constraint is applied unless `NOT VALID` is specified. |
| `NOT VALID` | Add a constraint without validating existing data. The constraint is still enforced when values are inserted or updated. A domain constraint added with `NOT VALID` cannot be validated later. Refer to [Known limitations](#known-limitations). |
| `DROP CONSTRAINT constraint_name` | Remove a constraint from the domain. With `IF EXISTS`, do not return an error if the constraint does not exist. |
| `RENAME CONSTRAINT constraint_name TO constraint_name` | Rename a constraint on the domain. |
| `OWNER TO` | Change the [role specification](/docs/stable/grant.md) for the domain's owner. |
| `RENAME TO name` | Rename the domain. |
| `SET SCHEMA` | Set [the schema](/docs/stable/sql-name-resolution.md) of the domain. |

## Required privileges

* To alter a domain, the user must be the owner of the domain.
* To set the schema of a domain, the user must also have the `CREATE` [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) on the new schema.
* To alter the owner of a domain:
  * The user executing the command must be a member of the new owner role.
  * The new owner role must have the `CREATE` privilege on the schema the domain belongs to.

## Examples

The examples use the following domain and table:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE DOMAIN order_qty AS INT CHECK (VALUE > 0);
```

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

Insert a row that will violate a stricter constraint added in a later example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO orders (qty) VALUES (100);
```

### Set a default value

The following statement sets a default value of `1` for columns that use the `order_qty` domain:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DOMAIN order_qty SET DEFAULT 1;
```

### Add a constraint

Existing data is validated before the constraint is applied. If a value in the `orders` table violates the new constraint, the statement returns an error:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DOMAIN order_qty ADD CONSTRAINT qty_cap CHECK (VALUE <= 50);
```

```
ERROR: column "qty" of table "orders" contains a value that violates domain order_qty check constraint "value <= 50"
SQLSTATE: 23514
```

With `NOT VALID`, existing data is not validated and only newly inserted or updated values are checked:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DOMAIN order_qty ADD CONSTRAINT qty_cap CHECK (VALUE <= 50) NOT VALID;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO orders (qty) VALUES (60);
```

```
ERROR: value for domain order_qty violates check constraint "qty_cap"
SQLSTATE: 23514
```

The `convalidated` column of `pg_catalog.pg_constraint` shows whether each domain constraint is validated:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT conname, convalidated FROM pg_constraint WHERE contypid = 'order_qty'::regtype;
```

```
      conname     | convalidated
------------------+---------------
  order_qty_check |      t
  qty_cap         |      f
(2 rows)
```

### Rename and drop a constraint

Rename the constraint added in the preceding example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DOMAIN order_qty RENAME CONSTRAINT qty_cap TO qty_max;
```

Drop the constraint by its new name:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DOMAIN order_qty DROP CONSTRAINT qty_max;
```

### Change the owner and schema

Create a role and make it the owner of the domain:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE ROLE sales_admin;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DOMAIN order_qty OWNER TO sales_admin;
```

Create a schema and move the domain into it:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA sales;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DOMAIN order_qty SET SCHEMA sales;
```

### Rename a domain

The following statement renames the domain, referencing it by its new schema-qualified name:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> ALTER DOMAIN sales.order_qty RENAME TO quantity;
```

The domain definition reflects the changes from the preceding examples:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT create_statement FROM [SHOW CREATE ALL TYPES];
```

```
                                           create_statement
-------------------------------------------------------------------------------------------------------
  CREATE DOMAIN sales.quantity AS INT8 DEFAULT 1:::INT8 CONSTRAINT order_qty_check CHECK (value > 0);
(1 row)
```

## Known limitations

* `ALTER DOMAIN ... VALIDATE CONSTRAINT` is not supported. A constraint added with `NOT VALID` cannot be validated later; to validate existing data, drop the constraint and add it without `NOT VALID`.
* For additional limitations, refer to [`DOMAIN`: Known limitations](/docs/stable/domain#known-limitations).

## See also

* [`DOMAIN`](/docs/stable/domain)
* [`CREATE DOMAIN`](/docs/stable/create-domain)
* [`DROP DOMAIN`](/docs/stable/drop-type#drop-a-domain)
* [Online Schema Changes](/docs/stable/online-schema-changes.md)