CREATE DOMAIN statement

Creates a domain in a database.

Syntax

CREATE { DOMAIN | DATATYPE } domain-name [ AS ] 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, or another domain

Parameters

DOMAIN | DATATYPE clause
It is recommended that you use CREATE DOMAIN, rather than CREATE DATATYPE, because CREATE DOMAIN is defined in the ANSI/ISO SQL Standard.
data-type
Set the data type to one of the builtin data types or to the data type of another domain by specifying that domain name. For example:
```
CREATE DOMAIN a INT;
CREATE DOMAIN b a;
```
You can also specify a %TYPE or %ROWTYPE attribute to set the data type to the data type of a column or row in a table or view. However, specifying a table reference variable for the %ROWTYPE (TABLE REF (table-reference-variable) %ROWTYPE) is not allowed.
NULL clause
This clause allows you to specify the nullability of a domain. When a domain is used to define a column, nullability is determined as follows:
- Nullability specified in the column definition.
- Nullability specified in the domain definition.
- If the nullability was not explicitly specified in either the column definition or the domain definition, then the setting of the allow_nulls_by_default option is used.
CHECK clause
When creating a domain with a CHECK constraint, you can use a variable name prefixed with the @ sign in the CHECK constraint's search condition. When the data type is used in the definition of a column, such a variable is replaced by the column name. This allows a domain's CHECK constraint to be applied to each table column defined with that domain.
AS USER clause
Specifies the owner of the object.

Remarks

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, but they are not collation-insensitive. For example, in the Turkish collation, the domain name "image" can be used (because it was created using lowercase letters) but not the domain "IMAGE". The letter case that is guaranteed to work is the letter case in which the type was created and this can be seen by looking at the type_name column of the SYS.SYSUSERTYPE table.

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.

The AS USER clause is generated into database unload scripts to annotate the creator of the domain since the creator is recorded in the SYSUSERTYPE system view. Otherwise, it is of no importance.

Privileges

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.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Domain support is optional ANSI/ISO SQL Language Feature F251.

Example

Some columns in a database table could be used for people's names and others to store addresses. You might define the following domains.

CREATE DOMAIN person CHAR(30) NOT NULL;
CREATE DOMAIN address CHAR(35);

The address domain differs from the person domain in the number of characters it can contain. Also, a column of type person cannot contain a NULL value.

Integer values are commonly used as unique identifiers for rows in tables. The following statement creates a domain named identifier, which is an unsigned integer that does not allow NULL values, and which is set to automatically increment by default.

CREATE DOMAIN identifier UNSIGNED INT
NOT NULL
DEFAULT AUTOINCREMENT;

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        identifier  PRIMARY KEY,
   Name      person,
   Street    address
);

In the above example, the table's primary key ID is an automatically incrementing non-NULL unsigned integer value (of course, primary keys can never be NULL).

Many of your tables may require similar identifier columns. Instead of specifying the same set of attributes each time, it is much more convenient to create the identifier domain and use this everywhere. The same can be said for the names of persons and their addresses. Maintenance of table schema is simplified by the use of domains.

Domains can be defined in terms of other domains. The following is an example..

CREATE DOMAIN simple_identifier UNSIGNED INT;
CREATE DOMAIN identifier simple_identifier NOT NULL DEFAULT AUTOINCREMENT;

The CREATE DOMAIN statements in the following example create domains based on the data types of the Name and Street columns of the myCustomers table. These new domains are used to define columns in the myCustomers2 table.

CREATE DOMAIN customers_name myCustomers.Name%TYPE NOT NULL;
CREATE DOMAIN customers_street myCustomers.Street%TYPE;
 
CREATE TABLE myCustomers2 (
   ID        identifier  PRIMARY KEY,
   Name      customers_name,
   Street    customers_street
);

In this example, only the data types are selected from myCustomers.Name and myCustomers.Street so the NOT NULL attribute is specified for the customers_name domain.

The order of creation of domains and tables is important. Obviously, the customers_name and customers_street domains cannot be created before the myCustomers table. Also, any adjustment to the definition of columns in the myCustomers table after the domains have been defined is not automatically carried over to the domain definitions.

When you create a domain, you can provide a CHECK constraint to ensure that no inappropriate values are entered into any column of this type. The following statement creates a domain named phone_number, 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 that does not start with 0 or 1, a 3-digit exchange that does not start with 0 or 1, and a 4-digit number separated by either dashes or blanks.

CREATE DOMAIN phone_number CHAR(12) NULL
CHECK( @phone_number 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})');

The following statement creates a ROW domain named MyRow, which can hold a row of data:

CREATE DOMAIN MyRow ROW( a INT, b INT );

The new MyRow domain can then be referenced in SQL statements. For example:

CREATE FUNCTION Swap( @parm MyRow ) 
   RETURNS (MyRow) 
     BEGIN            
        DECLARE @ret MyRow;            
        SET @ret = ROW( @parm.b, @parm.a );            
        RETURN @ret;
      END;

The following statement creates an ARRAY domain named MyArray, which can hold an array of rows:

CREATE DOMAIN MyArray ARRAY( 10 ) OF ROW( a INT, b INT );

Given the earlier declaration for MyRow, this example could just as well have been written as follows:

CREATE DOMAIN MyArray ARRAY( 10 ) OF MyRow;