Synopsis

Description

CREATE DOMAIN creates a new domain. A
domain is essentially a data type with optional constraints
(restrictions on the allowed set of values). The user who defines
a domain becomes its owner.

If a schema name is given (for example, CREATE DOMAIN myschema.mydomain ...) then the
domain is created in the specified schema. Otherwise it is
created in the current schema. The domain name must be unique
among the types and domains existing in its schema.

Domains are useful for abstracting common constraints on
fields into a single location for maintenance. For example,
several tables might contain email address columns, all requiring
the same CHECK constraint to verify the address syntax. Define a
domain rather than setting up each table's constraint
individually.

Parameters

name

The name (optionally schema-qualified) of a domain to be
created.

data_type

The underlying data type of the domain. This can include
array specifiers.

DEFAULT expression

The DEFAULT clause specifies a
default value for columns of the domain data type. The
value is any variable-free expression (but subqueries are
not allowed). The data type of the default expression must
match the data type of the domain. If no default value is
specified, then the default value is the null value.

The default expression will be used in any insert
operation that does not specify a value for the column. If
a default value is defined for a particular column, it
overrides any default associated with the domain. In turn,
the domain default overrides any default value associated
with the underlying data type.

CONSTRAINT constraint_name

An optional name for a constraint. If not specified, the
system generates a name.

NOT NULL

Values of this domain are not allowed to be null.

NULL

Values of this domain are allowed to be null. This is
the default.

This clause is only intended for compatibility with
nonstandard SQL databases. Its use is discouraged in new
applications.

CHECK (expression)

CHECK clauses specify integrity
constraints or tests which values of the domain must
satisfy. Each constraint must be an expression producing a
Boolean result. It should use the key word VALUE to refer to the value being
tested.

Compatibility

See Also

Submit correction

If you see anything in the documentation that is not correct, does not match
your experience with the particular feature or requires further clarification,
please use
this form
to report a documentation issue.