ALTER DOMAIN

The ALTER DOMAIN modifies a in the current database.

The ALTER DOMAIN statement performs a schema change. For more information about how online schema changes work in CockroachDB, refer to .

Synopsis

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, , 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 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.
`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 for the domain’s owner.
`RENAME TO name`	Rename the domain.
`SET SCHEMA`	Set 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 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:

> CREATE DOMAIN order_qty AS INT CHECK (VALUE > 0);

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

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

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

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

> ALTER DOMAIN order_qty ADD CONSTRAINT qty_cap CHECK (VALUE <= 50) NOT VALID;

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

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

> ALTER DOMAIN order_qty RENAME CONSTRAINT qty_cap TO qty_max;

Drop the constraint by its new name:

> ALTER DOMAIN order_qty DROP CONSTRAINT qty_max;

Change the owner and schema

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

> CREATE ROLE sales_admin;

> ALTER DOMAIN order_qty OWNER TO sales_admin;

Create a schema and move the domain into it:

> CREATE SCHEMA sales;

> ALTER DOMAIN order_qty SET SCHEMA sales;

Rename a domain

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

> ALTER DOMAIN sales.order_qty RENAME TO quantity;

The domain definition reflects the changes from the preceding examples:

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

Statements

Syntax

Data Types

Spatial Data

Constraints

Synopsis

Parameters

Required privileges

Examples

Set a default value

Add a constraint

Rename and drop a constraint

Change the owner and schema

Rename a domain

Known limitations

See also

​Synopsis

​Parameters

​Required privileges

​Examples

​Set a default value

​Add a constraint

​Rename and drop a constraint

​Change the owner and schema

​Rename a domain

​Known limitations

​See also

Synopsis

Parameters

Required privileges

Examples

Set a default value

Add a constraint

Rename and drop a constraint

Change the owner and schema

Rename a domain

Known limitations

See also