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

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.

You must have the CREATE DATATYPE or CREATE ANY OBJECT system privilege to create domains owned by you. You cannot create domains owned by others.

Automatic commit.

• allow_nulls_by_default option
• 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 set to autoincrement 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})');

Some columns in a database are used for employee names and others to store addresses. You might then define the following domains.

CREATE DOMAIN persons_name CHAR(30)
CREATE DOMAIN street_address CHAR(35);

Having defined these domains, you can use them much as you would the built-in data types. You can use these definitions to define a table, as follows. You need the CREATE TABLE privilege to execute the following statement.

CREATE TABLE myCustomers (
   ID INT  DEFAULT AUTOINCREMENT  PRIMARY KEY,
   Name persons_name,
   Street street_address);

In the above example, the table's primary key is specified to be of type integer. Indeed, many of your tables may require similar identifiers. Instead of specifying that these are integers, it is much more convenient to create an identifier domain for use in these applications.

When you create a domain, you can specify a default value and provide check constraint to ensure that no inappropriate values are typed into any column of this type.

Integer values are commonly used as table identifiers. A good choice for unique identifiers is to use positive integers. Since such identifiers are likely to be used in many tables, you could define the following domain.

CREATE DOMAIN identifier UNSIGNED INT
DEFAULT AUTOINCREMENT;

Using this definition, you can rewrite the definition of the Customers table, shown above.

CREATE TABLE Customers2 (
   ID identifier PRIMARY KEY,
   Name persons_name,
   Street street_address
);