Creating Domains

---

Domains are used as data types in column definitions when creating tables in order to:

• assist in keeping the database consistent
• validate the data (particular values or data type) accepted in the columns
• define default values for columns.

Create Domain Statement

The statement for creating domains has the general form:

 CREATE DOMAIN domain-name
    AS data-type
    [DEFAULT default-value]
    [{[CONSTRAINT constraint_name] CHECK (check-condition)}...];
 

• The CREATE DOMAIN clause defines the domain name.
• The AS clause defines the domain data type.
• The DEFAULT clause defines a default value for the domain
• The CHECK clause defines the domain limits.

It is a good practice for maintaining the integrity of the database to define domains for as many columns as possible.

Domains with a Default Value

The default clause defines values that are inserted into the column when an explicit value is not specified or the keyword DEFAULT is used in an INSERT statement.

Examples

Define the default value '000000' for the domain SIXDIGITS:

 CREATE DOMAIN sixdigits AS CHAR(6) DEFAULT '000000';

Define the session user's name as the default value for the domain USER_NAME:

 CREATE DOMAIN user_name AS VARCHAR(128)
    DEFAULT SESSION_USER;

Domains with a Check Clause

Domains defining default values can also include check clauses. You could define the SOUNDEX domain as:

 CREATE DOMAIN sixdigits AS CHAR(6) DEFAULT '000000'
                            CHECK (VALUE IS NOT NULL)
                            CHECK (CHAR_LENGTH(TRIM(VALUE)) = 6
                                   AND VALUE BETWEEN '000000' AND '999999');
 

This means that the NULL indicator will not be accepted into columns belonging to this domain and that the value must be a character string of six digits.

If the default value is defined as being outside the check constraint this ensures that an explicit value must always be inserted into the column.

Searching and Check Clauses

Specification of a CHECK clause means that only values for which the specified search condition evaluates to true may be assigned to a column belonging to the domain.

The search condition, see the Mimer SQL Reference Manual, Search Conditions, in the CHECK clause may only reference the domain values (by using the keyword VALUE), constants, or the keywords CURRENT_USER, SESSION_USER and NULL.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se