CREATE DOMAIN

---

Creates a domain.

where check-clause is:

Usage

Embedded/Interactive/ODBC/JDBC.

Description

A domain is created with the properties specified in the statement. Domains may be used instead of explicit data type specifications to define column formats in the CREATE and ALTER TABLE statements.

If domain-name is specified in its unqualified form, the domain will be created in the schema which has the same name as the current ident.

If domain-name is specified in its fully qualified form (i.e. schema-name.domain-name) the domain will be created in the named schema (in this case, the current ident must be the creator of the specified schema).

Refer to Data Types in SQL Statements for a description of how the various data types are specified for the domain.

If default-value is specified, this value will be assigned to a column defined using the domain whenever a new table row is created or an existing table row is updated without an explicit value being specified for that column.

The COLLATE Clause

If the COLLATE clause is specified, the data controlled by the domain will be ordered and compared according to the collation specified.

For more information, see the Mimer SQL User's Manual, Collations.

The CHECK Clause

Specification of a CHECK clause means that only values for which the search condition does not evaluate to false may be assigned to a column defined using the domain.

The search condition, see Search Conditions, in the CHECK clause may only reference the domain (by using the keyword VALUE), literals, user-defined function invocations, method invocations or the keyword NULL. The CHECK clause must not contain any non-deterministic expressions, e.g. CURRENT_DATE.

References to columns, subselects, set functions or host variables are not allowed.

Specifying INITIALLY IMMEDIATE NOT DEFERRABLE explicitly states that the check constraint will be, by default, verified at the time the relevant data manipulation operation is performed rather than when the transaction is committed and that the verification may never be explicitly deferred until the time the transaction is committed. This is also the default behavior. (This is to allow for future extensions to the Mimer SQL syntax.)

Language Elements

default-value, see Default Values.

Restrictions

An ident must have USAGE privilege on the domain in order to use it.

Notes

The domain name may not be the same as the name of any other domain belonging to the same schema.

The CREATE DOMAIN statement does not verify that any specified default value conforms to the restrictions of any specified CHECK clause. It is, therefore, possible to create a domain definition where attempts to store the default value in a column defined using the domain will fail.

Examples

 CREATE DOMAIN name AS INTEGER CHECK (VALUE IN (-1,0,3)
                                      OR VALUE BETWEEN 5 AND 9)
 
 CREATE DOMAIN name AS NCHAR VARYING(48) COLLATE english_1
                       CHECK (CHARACTER_LENGTH(VALUE) > 0)

Standard Compliance

Standard	Compliance	Comments
SQL-2011	Features outside core	Feature F251, "Domain support".

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