Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


Creating Domains


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

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)];
 

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 SOUNDEX:
 CREATE DOMAIN soundex 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 soundex 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, Chapter 9, 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.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX