Creates a domain.
where check-clause is:
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, chapter 6, 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 or the keyword NULL. The CHECK clause must not contain any non-deterministic expressions, e.g. CURRENT_DATE.
ExampleCHECK (VALUE IN (-1,0,3) OR VALUE BETWEEN 5 AND 9)
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.)
default-value, see Default Values.
An ident must have USAGE privilege on the domain in order to use it.
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.
ExampleCREATE DOMAIN name AS VARCHAR(48) DEFAULT NULL CONSTRAINT name_value_is_null CHECK (VALUE IS NOT NULL) COLLATE english
X/Open-95 MIMER EXTENSION The CREATE DOMAIN statement is a Mimer SQL extension. SQL-92 YES Fully compliant.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40