CREATE { DOMAIN | DATATYPE } [ AS ] domain-name data-type
[ [ NOT ] NULL ]
[ DEFAULT default-value ]
[ CHECK ( condition ) ]

domain-name : identifier

data-type :  built-in data type, with precision and scale

Domains are aliases for built-in data types, including precision and scale values where applicable. They improve convenience and encourage consistency in the database.

Domains are objects within the database. Their names must conform to the rules for identifiers. Domain names are always case insensitive, as are built-in data type names.

The user who creates a data type is automatically made the owner of that data type. No owner can be specified in the CREATE DATATYPE statement. The domain name must be unique, and all users can access the data type without using the owner as prefix.

Domains can have CHECK conditions and DEFAULT values, and you can indicate whether the data type permits NULL values or not. These conditions and values are inherited by any column defined on the domain. Any conditions or values explicitly specified in the column definition override those specified for the domain.

To drop the domain from the database, use the DROP DOMAIN statement. You must be either the owner of the domain, or have DBA authority, to drop a domain.

RESOURCE authority.

Automatic commit.

• DROP DOMAIN statement
• SQL data types

The following statement creates a domain named address, which holds a 35-character string, and which may be NULL.

CREATE DOMAIN address CHAR( 35 ) NULL;

The following statement creates a domain named ID, which does not allow NULLS, and which is autoincremented by default.

CREATE DOMAIN ID INT
NOT NULL
DEFAULT AUTOINCREMENT;

The following statement creates a domain named PhoneNumber, which uses a regular expression within a CHECK constraint to ensure that the string has a properly-formatted North American phone number of 12 characters, consisting of a 3-digit area code, 3-digit exchange, and 4-digit number separated by either dashes or a blank.

CREATE DOMAIN PhoneNumber CHAR(12) NULL
CHECK( @PhoneNumber REGEXP '([2-9][0-9]{2}-[2-9][0-9]{2}-[0-9]{4})|([2-9][0-9]{2}\s[2-9][0-9]{2}\s[0-9]{4})');