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

# ALTER DOMAIN

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 <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `ALTER DOMAIN` <InternalLink path="sql-statements">statement</InternalLink> modifies a <InternalLink path="domain">domain data type</InternalLink> 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 <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>.
</Note>

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/ccT6XAedsjs6XuK5/images/sql-diagrams/v26.3/alter_domain.svg?fit=max&auto=format&n=ccT6XAedsjs6XuK5&q=85&s=ac40b424cc82a92d109a85521d8dee8f" alt="alter_domain syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="869" height="619" data-path="images/sql-diagrams/v26.3/alter_domain.svg" />

## 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, <InternalLink path="user-defined-functions">user-defined functions</InternalLink>, 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 <InternalLink path="check">`CHECK`</InternalLink> 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 <InternalLink path="grant">role specification</InternalLink> for the domain's owner.                                                                                                                                                                                                                                          |
| `RENAME TO name`                                       | Rename the domain.                                                                                                                                                                                                                                                                                                                       |
| `SET SCHEMA`                                           | Set <InternalLink path="sql-name-resolution">the schema</InternalLink> 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` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> 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 <InternalLink path="domain#known-limitations">`DOMAIN`: Known limitations</InternalLink>.

## See also

* <InternalLink path="domain">`DOMAIN`</InternalLink>
* <InternalLink path="create-domain">`CREATE DOMAIN`</InternalLink>
* <InternalLink path="drop-type#drop-a-domain">`DROP DOMAIN`</InternalLink>
* <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>