www.mimer.com/developer

Creating Domains

---

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

• to assist in keeping the database consistent
• to limit the data (particular values or data type) accepted in the columns
• to 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 '-ND-' ('not defined') for the domain
ROOMTYPE:

 CREATE DOMAIN ROOMTYPE
        AS CHAR(4)
        DEFAULT '-ND-';

Define the current user's name as the default value for the domain NAME:

 CREATE DOMAIN NAME AS CHAR(128)
        DEFAULT CURRENT_USER;

Define the domain CHARGE_PERIOD_VALUE which uses the sequence CHARGE_PERIOD_NO_SEQUENCE to provide a default value:

 CREATE DOMAIN CHARGE_PERIOD_VALUE AS INTEGER
        DEFAULT CURRENT_VALUE OF CHARGE_PERIOD_NO_SEQUENCE;

Domains with a Check Clause

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

 CREATE DOMAIN ROOMTYPE
               AS CHAR(4)
               DEFAULT '-ND-'
               CHECK (VALUE IS NOT NULL);
 

This means that the NULL indicator will not be accepted into columns belonging to this domain.

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.

The domain CALENDAR, created below, uses a check clause to limit the range of accepted values:

 CREATE DOMAIN CALENDAR
        AS     DATE
        CHECK (VALUE BETWEEN DATE '1996-01-01' AND
                             DATE '2099-12-31');

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, 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.

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se