Creates a domain definition.
A domain defines the set of values, character set, collating
sequence, and formatting clause that a column in a table can
have. The CREATE DOMAIN statement specifies the set of values by
associating a data type with a domain name.
There are two ways to specify a domain definition:
o With a domain name, data type, and any combination of the
following optional clauses:
- Default value
- Stored name
- Collating sequence
- Formatting clauses such as EDIT STRING or QUERY HEADER
o With the FROM clause and a repository path name that refers to
a field already defined in the repository
When the CREATE DOMAIN statement executes, SQL adds the domain
definition to the database.
If you attached to the database with the PATHNAME specification,
the domain definition is also added to the repository.
You can refer to a domain instead of an SQL data type in the
CREATE and ALTER TABLE statements, and in formal parameter
declarations in functions and procedures. If the domain has to
change, you need only change that one domain definition (using
the ALTER DOMAIN statement) to change all the tables. This
ability makes it easier to keep applications consistent.
A domain can be referenced in the following locations:
o CREATE, ALTER and DROP DOMAIN statements
o CREATE and ALTER TABLE statements as the data type for a
column
o CREATE and ALTER MODULE statements as the data type of a
routine parameter, or the data type of declared variable
o CREATE FUNCTION statement as the data type of a function
parameter or function result
o CREATE PROCEDURE statement as the data type of a procedure
parameter
o CREATE and ALTER SYNONYM statement as the base object for a
synonym
o as the datatype of a CAST expression
o as a data type of a DECLARE variable statement in interactive
SQL
o as the source in the EDIT USING clause of the SELECT and PRINT
statements in interactive SQL. The EDIT STRING is inherited
from that domain.
1 – Environment
You can use the CREATE DOMAIN statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
CREATE DOMAIN -+
+--------------+
+-> <domain-name> +-----------------------------------++
| +-> STORED NAME IS <stored-name> ---+|
|+-----------------------------------------------------+
|++-> IS data-type -+-+----------------------------+---+
| +-> AS data-type -+ +--> DEFAULT value-expr -----+ |
| +--------------------------------------------------+
| +-+--------------------------------------------+-+
| +-> COLLATING SEQUENCE IS <collation-name> --+ |
| +-> NO COLLATING SEQUENCE -------------------+ |
| +------------------------------------------------+
| +-+----------------------+-+------------------------++->
| +-> domain-constraint -+ ++> sql-and-dtr-clause -++|
| +---------<------------+ |
+-> FROM -> <path-name> +----------------->----------+---+
+-> DATABASE ALIAS <alias> --+
domain-name =
-+---------------------------+-> <name-of-domain> --->
+-+-> <schema-name> -+-> . -+
+-> <alias> -------+
data-type =
--+-> char-data-types ---------------------------+-->
+-> TINYINT --------------+-----+----------+---+
+-> SMALLINT -------------+ +-> (<n>) -+ |
+-> INTEGER --------------+ |
+-> BIGINT ---------------+ |
+-> LIST OF BYTE VARYING -+ |
+-> DECIMAL -++-----------------------------+--+
+-> NUMERIC -++-> ( -> <n> +----------+-> ) + |
| +-> , <n> -+ |
+-> FLOAT -+----------+------------------------+
| +-> (<n>) -+ |
+-> REAL --------------------------------------+
+-> DOUBLE PRECISION --------------------------+
+-> date-time-data-types ----------------------+
char-data-types =
-+-> CHAR -------------++------------++--------------------------------+-+->
+-> CHARACTER --------++-> ( <n> ) -++-> CHARACTER SET char-set-name -+ |
+-> CHAR VARYING -----+ |
+-> CHARACTER VARYING + |
+-> VARCHAR --+> ( <n> ) ---+--------------------------------+----------+
+-> VARCHAR2 -+ +-> CHARACTER SET char-set-name -+ |
+-> LONG VARCHAR ------------------------------------------------------+
+-> NCHAR --------------+-+------------+--------------------------------+
+-> NATIONAL CHAR ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER -+ |
+-> NCHAR VARYING --------------+-+------------+------------------------+
+-> NATIONAL CHAR VARYING ------+ +-> ( <n> ) -+ |
+-> NATIONAL CHARACTER VARYING -+ |
+-> RAW -> ( <n> ) -----------------------------------------------------+
+-> LONG -+--------+----------------------------------------------------+
+-> RAW -+
date-time-data-types =
--+-> DATE -+----------+-----------------+-->
| +-> ANSI -+ |
| +-> VMS ---+ |
+-> TIME ---> frac --------------------+
+-> TIMESTAMP --> frac ----------------+
+-> INTERVAL ---> interval-qualifier --+
interval-qualifier =
--+-> YEAR ---> prec --+-------------+-----------------+->
| +-> TO MONTH -+ |
+-> MONTH --> prec ----------------------------------+
+-> DAY ----> prec --+-------------------------------+
| +-> TO -+-> HOUR ---------------+
| +-> MINUTE -------------+
| +-> SECOND -> frac -----+
+-> HOUR ---> prec --+-------------------------------+
| +-> TO -+-> MINUTE -------------+
| +-> SECOND -> frac -----+
+-> MINUTE -> prec --+-------------------------------+
| +-> TO SECOND ------> frac -----+
+-> SECOND -> seconds-prec --------------------------+
literal =
--+-> numeric-literal ----+--->
+-> string-literal -----+
+-> date-time-literal --+
+-> interval-literal ---+
domain-constraint =
--+----------------------------------------------------+---->
+-> CHECK ( predicate ) -+--------------------------++
+-> constraint-attributes -+
sql-and-dtr-clause =
-+-> QUERY HEADER IS -+> <quoted-string> +------------------+->
| +------ / <--------+ |
+-> EDIT STRING IS <quoted-string> ------------------------+
| |
+-> QUERY NAME FOR -+-> DTR --------+-> IS <quoted-string> +
| +-> DATATRIEVE -+ |
+-> DEFAULT VALUE FOR -+-> DTR --------+-> IS <literal> ---+
| +-> DATATRIEVE -+ |
+-> NO QUERY HEADER ---------------------------------------+
+-> NO EDIT STRING ----------------------------------------+
+-> NO QUERY NAME ----+--> FOR -+-> DTR --------+----------+
+-> NO DEFAULT VALUE -+ +-> DATATRIEVE -+ |
+-> COMMENT IS -+-> <quoted-string> -+---------------------+
+------ / <----------+
3 – Arguments
3.1 – char-data-types
A character type. See the Data_Types HELP topic for more
information on data types.
3.2 – character-set-name
A valid character set.
3.3 – COLLATING_SEQUENCE
Specifies a collating sequence for the named domain.
The OpenVMS National Character Set (NCS) utility provides a
set of predefined collating sequences and also lets you define
collating sequences of your own. The COLLATING SEQUENCE clause
accepts both predefined and user-defined NCS collating sequences.
Before you use the COLLATING SEQUENCE clause in a CREATE DOMAIN
statement, you must first specify the NCS collating sequence for
SQL using the CREATE COLLATING SEQUENCE statement. The sequence-
name argument in the COLLATING SEQUENCE clause must be the same
as the sequence-name in the CREATE COLLATING SEQUENCE statement.
3.4 – COMMENT_IS
Adds a comment about the domain. SQL displays the text of the
comment when it executes a SHOW DOMAIN statement. Enclose the
comment in single quotation marks ( ') and separate multiple
lines in a comment with a slash mark (/).
3.5 – DATABASE ALIAS alias
In the FROM path-name clause, specifies the name for an attach
to a particular database. SQL adds the domain definition to the
database referred to by the alias.
If you do not specify an alias, SQL adds the domain definition to
the default database. See the User_Supplied_Names HELP topic for
more information on default databases and aliases.
3.6 – date-time-data-types
A data type that specifies a date, time, or interval. See the
Data_Types HELP topic for more information about date-time data
types.
3.7 – DEFAULT value-expr
Provides a default value for a domain.
You can use any value expression including subqueries,
conditional, character, date/time, and numeric expressions as
default values. See Value Expressions for more information about
value expressions.
For more information about NULL, see the NULL_Keyword HELP topic.
The value expressions described in Value Expressions include
DBKEY and aggregate functions. However, the DEFAULT clause is
not a valid location for referencing a DBKEY or an aggregate
function. If you attempt to reference either, you receive a
compile-time error.
If you do not specify a DEFAULT for a column, it inherits the
DEFAULT from the domain. If you do not specify a default for
either the column or domain, SQL assigns NULL as the default
value.
3.8 – domain-constraint
Creates a constraint for the named domain.
Specify a domain constraint when you create a domain to limit
which values can be stored in columns based on the domain. Domain
constraints specify that columns based on the domain contain only
certain data values or that data values can or cannot be null.
Use the CHECK clause to specify that a value must be within a
specified range or that it matches a list of values. When you
specify a CHECK clause for a domain constraint, you ensure that
all values stored in columns based on the domain are checked
consistently.
3.9 – domain-name
The name of a domain you want to create. The domain name must be
unique among domain names in the schema. You can qualify it with
an alias or (in multischema databases only) a schema name.
3.10 – FROM pathname
Specifies the repository path name of a repository field
definition. SQL creates the domain using the definition from
this field and gives the domain the name of the field definition.
Creating a domain based on a repository domain definition is
useful when many applications share the same definition. Changes
to the common definition can be automatically reflected in all
applications that use it.
You can create a domain using the FROM path-name clause only if
the field definition in the repository was originally created
using the repository CDO utility. For instance, you cannot create
a domain using the FROM path-name clause if the definition was
created in the repository as part of an SQL session. Oracle
Rdb requires that the field names referenced in the VALID IF
expression of the CDO utility match the name of the global field
being defined or changed.
NOTE
Changes by other users or applications to the field
definition in the repository will affect the domain
definition once the database is integrated to match the
repository with an INTEGRATE DATABASE . . . ALTER FILES
statement.
You can use the FROM path-name clause only if the database was
attached specifying PATHNAME. You can specify either a full
repository path name or a relative repository path name.
You cannot specify formatting clauses when you use the FROM path-
name form of the CREATE DOMAIN statement.
You cannot use the FROM path-name clause when embedding a CREATE
DOMAIN statement in a CREATE DATABASE statement.
3.11 – data-type
Syntax options:
IS data-type
AS data-type
A valid SQL data type. See the Data_Types HELP topic for more
information on data types.
3.12 – NO_COLLATING_SEQUENCE
Specifies that this domain uses the standard default collating
sequence, that is, ASCII. Use the NO COLLATING SEQUENCE clause to
override the collating sequence defined for the database in the
CREATE DATABASE or ALTER DATABASE statement.
3.13 – sql-and-dtr-clause
Optional SQL and DATATRIEVE formatting clause. See the DATATRIEVE
HELP topic for more information on formatting clauses.
3.14 – STORED_NAME_IS
Specifies a name that Oracle Rdb uses to access a domain created
in a multischema database. The stored name lets you access
multischema definitions using interfaces, such as Oracle RMU,
the Oracle Rdb management utility, that do not recognize multiple
schemas in one database. You cannot specify a stored name for a
domain in a database that does not allow multiple schemas. For
more information about stored names, see the User_Supplied_Names
HELP topic.
4 – Examples
Example 1: Creating a domain for a standard EMPLOYEE_ID
definition
The following example creates the domain ID_DOM, which will be a
standard definition of columns for the employee ID:
SQL> CREATE DOMAIN ID_DOM CHAR(5)
SQL> COMMENT IS
cont> 'standard definition of employee id';
Example 2: Creating a domain for standard date
The following example creates the domain STANDARD_DATE_DOM, which
includes the edit string DD-MMM-YYYY:
SQL> CREATE DOMAIN STANDARD_DATE_DOM DATE
cont> EDIT STRING IS 'DD-MMM-YYYY'
SQL> COMMENT IS
cont> 'standard definition for complete dates';
Example 3: Creating domains with default values
The following example creates two domains: ADDRESS_DATA2_DOM and
WORK_STATUS_DOM. The ADDRESS_DATA2_DOM domain has a default value
of NULL; the WORK_STATUS_DOM domain has a default value of 1 to
signify full-time work status.
SQL> CREATE DOMAIN ADDRESS_DATA2_DOM CHAR(20)
cont> DEFAULT NULL;
SQL> --
SQL> CREATE DOMAIN WORK_STATUS_DOM SMALLINT
cont> DEFAULT 1;
Example 4: Basing a domain on a repository field definition
The following example illustrates using the repository as a
source for the definition in a CREATE DOMAIN statement:
$ SQL$
SQL> ATTACH 'PATHNAME CDD$TOP.SQL.RDB.TEST.DATE';
SQL> CREATE DOMAIN FROM DOMAIN_TEST;
SQL> SHOW DOMAIN
User domains in database with pathname
SYS$COMMON:[CDDPLUS]SQL.RDB.TEST.DATE;1
DOMAIN_TEST BIGINT
Example 5: Creating a domain with a collating sequence
The following example creates a domain with the predefined NCS
collating sequence SPANISH. Note that you must first execute the
CREATE COLLATING SEQUENCE statement:
SQL> --
SQL> CREATE COLLATING SEQUENCE SPANISH SPANISH;
SQL> CREATE DOMAIN LAST_NAME_SPANISH CHAR(14)
cont> COLLATING SEQUENCE IS SPANISH;
SQL> --
SQL> SHOW DOMAIN LAST_NAME_SPANISH
LAST_NAME_SPANISH CHAR(14)
Collating sequence: SPANISH
Example 6: Creating a domain using the database default character
set
For each of the following examples, assume the database was
created specifying the database default character set as DEC_
KANJI and the national character set as KANJI.
The following example creates the domain DEC_KANJI_DOM using the
database default character set:
SQL> SHOW CHARACTER SET;
Default character set is DEC_KANJI
National character set is KANJI
Identifier character set is DEC_KANJI
Literal character set is DEC_KANJI
Alias RDB$DBHANDLE:
Identifier character set is DEC_KANJI
Default character set is DEC_KANJI
National character set is KANJI
SQL> CREATE DOMAIN DEC_KANJI_DOM CHAR(8);
SQL> SHOW DOMAIN
User domains in database with filename MIA_CHAR_SET
DEC_KANJI_DOM CHAR(8)
Because the CREATE DOMAIN statement does not specify a character
set, Oracle Rdb defines the domain using the database default
character set. The database default character set does not
display with the SHOW DOMAIN statement.
An equivalent statement to the previous CREATE DOMAIN statement
is:
SQL> CREATE DOMAIN DEC_KANJI_DOM CHAR(8) CHARACTER SET DEC_KANJI;
Example 7: Creating a domain using the national character set
The following example creates the domain KANJI_DOM using the
NCHAR data type to designate use of the national character set:
SQL> CREATE DOMAIN KANJI_DOM NCHAR(8);
SQL> SHOW DOMAIN
User domains in database with filename MIA_CHAR_SET
DEC_KANJI_DOM CHAR(8)
KANJI_DOM CHAR(8)
KANJI 8 Characters, 16 Octets
When a character set other than the default is specified, the
SHOW DOMAIN statement displays the character set associated with
the domain.
Two statements equivalent to the previous CREATE DOMAIN statement
are:
SQL> CREATE DOMAIN KANJI_DOM NATIONAL CHAR(8);
SQL> CREATE DOMAIN KANJI_DOM CHAR(8) CHARACTER SET KANJI;
Example 8: Creating a domain constraint
The following example creates a domain constraint:
SQL> -- The SET DIALECT 'SQL99' statement sets the default date format
SQL> -- to the ANSI/ISO SQL standard format.
SQL> --
SQL> SET DIALECT 'SQL99';
SQL> --
SQL> -- The following domain ensures that any dates inserted into the database
SQL> -- are later than January 1, 1900:
SQL> --
SQL> CREATE DOMAIN TEST_DOM DATE
cont> DEFAULT NULL
cont> CHECK (VALUE > DATE'1900-01-01' OR
cont> VALUE IS NULL)
cont> NOT DEFERRABLE;
SQL>
SQL> -- The following example creates a table with one column based on the
SQL> -- domain TEST_DOM:
SQL> --
SQL> CREATE TABLE DOMAIN_TEST
cont> (DATE_COL TEST_DOM);
SQL> --
SQL> -- SQL returns an error if you attempt to insert data that does not
SQL> -- conform to the domain constraint:
SQL> --
SQL> INSERT INTO DOMAIN_TEST
cont> VALUES (DATE'1899-01-01');
%RDB-E-NOT_VALID, validation on field DATE_COL caused operation to fail