SAND CDBMS SQL Reference Guide
CREATE DOMAIN

 

The CREATE DOMAIN statement creates a new domain in which values for table columns can be stored in a database.


Required Privileges

To create a new local domain, the user authorization must own or possess OWNER privileges on the schema to which the domain will belong, or possess DBA privileges.

If the new domain is associated with a partitioned table and distributed, the users defined for the connections to the remote nodes must own or possess OWNER privileges on the schema where the domain will be replicated, or possess DBA privileges.


Syntax


CREATE DOMAIN | CREATE LOCAL DOMAIN
Outside of MPP mode, CREATE DOMAIN and CREATE LOCAL DOMAIN function the same way, creating a new domain in the current database. In MPP mode, the CREATE DOMAIN syntax creates a local domain that is automatically replicated across all partition nodes. In MPP mode, to create a non-distributed domain (local only), the CREATE LOCAL TABLE syntax must be used.

domain name
Is a name for the new domain. The domain name argument is required, and must be unique among all domain names in the schema. The domain name can be up to 128 characters long. It is strongly recommended that none of the SAND CDBMS SQL keywords be used as a domain name, as this may cause problems when referencing the domain in certain SQL statements.

The domain will belong to the current schema unless the domain name is qualified by a schema name (that is, schema-name.domain-name), in which case the domain will belong to the specified schema.

DIMENSION OF partition name
Identifies the partitioned table with which the distributed domain will be associated. The new domain will be copied to each remote node defined for the specified partitioned table.

Note that if this clause is omitted in MPP mode, the domain will be created in the context of the default partitioned table. When not in MPP mode, creating a distributed domain requires the inclusion of the DIMENSION OF clause to specify a partitioned table.

data type
Indicates the data type of the values that will be stored in the created domain. The AS keyword is optional and does not affect the statement in any way. The data type argument must be a valid SAND CDBMS SQL data type, with length and precision arguments where appropriate (consult the SAND CDBMS Data Types section for more information).

collation table
The optional COLLATE collation table argument allows the specification of a character repertoire to be used for the domain, and is designed to enable the proper sorting of non-English characters.

The collation table argument can be one of the following:

BINARY
Enables the standard ASCII sequence (with the set of uppercase characters sorted before those in lowercase). This is the default for SAND CDBMS domains.

BINARY_UPCASE
Converts the lowercase characters of the standard repertoire (a-z) to uppercase (A-Z), allowing for sorting of character data in “dictionary order”.

LATIN01
Provides the expected sort order for the accented characters of non-English Western European languages, with the set of uppercase characters sorted before those in lowercase. This collation table conforms to the Latin-1 (ISO/IEC 8859-1) standard.

LATIN01_UPCASE
Converts the lowercase characters of the LATIN01 repertoire to their uppercase equivalents, just as the BINARY_UPCASE collation table does for the ASCII characters.

LATIN02
Provides the expected sort order for the accented characters of non-English Eastern European languages, with the set of uppercase characters sorted before those in lowercase. This collation table conforms to the Latin-2 (ISO/IEC 8859-2) standard.

LATIN02_UPCASE
Converts the lowercase characters of the LATIN02 repertoire to their uppercase equivalents, just as the BINARY_UPCASE collation table does for the ASCII characters.

constant
The optional DEFAULT constant argument specifies a literal value as the default for columns based on the domain. A numeric value may be preceded by a unary operator (that is, a + or –) to indicate whether it is a positive or negative value. A character string or date/time literal must be surrounded by single quotation marks. The default value must be compatible with the data type of the domain.

special constant
The optional DEFAULT special constant argument specifies one of the special system variables supported by SAND:

The special constant must be compatible with the data type of the domain.


Description

Once a domain has been created, it may be referenced in CREATE TABLE or ALTER TABLE statements as the domain that will contain the values for one or more columns. Users must own, or have USAGE privileges on, a domain in order to specify it in a column definition.

The new domain can be defined as distributed (DIMENSION OF a partitioned table), in which case the domain will also be copied to the remote databases associated with the table. On each node, the replicated domain will have the same name and will belong to the same schema as the local domain. Because of this, the domain's schema must exist on all of the nodes, and that schema must not already contain a domain with the same name, otherwise the CREATE DOMAIN statement will fail.

In MPP mode, the domain is distributed to other nodes unless the CREATE LOCAL DOMAIN syntax is used. Outside of MPP mode, the reverse is the case: the domain is considered local only, unless the DIMENSION OF clause is specified. If the DIMENSION OF clause is omitted in MPP mode, a distributed domain is created in the context of the user's default partitioned table; otherwise the new domain is associated with the partitioned table specified by the DIMENSION OF clause.

Note that the CREATE LOCAL DOMAIN syntax and the DIMENSION OF clause cannot appear together in the same SQL statement, since they describe contradictory actions.

An optional COLLATE clause can be included in CREATE DOMAIN statements that create domains based on the CHAR(x), CHARACTER(x), VARCHAR(x), CHAR VARYING(x), or CHARACTER VARYING(x) data types. The specified character repertoire (view the options above) is designed to enable the proper sorting of non-English characters.

The optional DEFAULT clause sets the default value for all columns that reference the created domain. If a column defined on such a domain does not have a value specified for it during an insert operation, the domain default will be used for the column. If both a column and the domain on which the column is based have default values defined, the column default takes precedence.

The default value can either be a literal, one of the special constants (see the list above), or a null.

Note that it is not possible to specify both COLLATE and DEFAULT options. One or the other can be specified in the same CREATE DOMAIN statement.

Refer to SAND CDBMS SQL Database Objects and Language Elements: Domains for more information about SAND CDBMS domains.


Examples

The following statement creates a new domain (d_name) in schema vip, of data type CHARACTER, maximum length 15:

CREATE DOMAIN vip.d_name CHAR(15) COLLATE latin01;

The COLLATE clause above specifies that the Latin collation table will be used for this domain.

The following statement creates (in the current schema) an INTEGER domain, named d_int, whose default value is 0:

CREATE DOMAIN d_int INTEGER DEFAULT 0;

Any subsequent column defined on domain d_int will have the value 0 inserted when an inserted record omits a value for the column.

The following command creates a domain named d_key in schema s1 of data type CHAR(5):

CREATE DOMAIN s1.d_key DIMENSION OF xpart CHAR(5);

Since the domain is defined as DIMENSION OF partitioned table xpart, the domain will be created in the current database, as well as in each remote database that contains a table partition of xpart. Note that the domain will have the same name and will belong to the same schema on each remote node; therefore the schema must already exist on all of the nodes, and that schema must not contain a domain with the same name.

If xpart is defined as the default partitioned table of the user executing the command above, the DIMENSION OF clause is unnecessary: the domain will be created as a dimension of the table anyway. However, if the user does not have a default partitioned table, or if the domain is being created as a dimension of a partitioned table other than the user's default, the DIMENSION OF clause is required.

On the other hand, if the user has a default partitioned table and does not want a new domain to be replicated on other nodes, the CREATE LOCAL DOMAIN command can be used:

CREATE LOCAL DOMAIN s1.scode CHAR(8);

Here, a new domain named scode is being created in schema s1 of type CHAR(8). Because the LOCAL keyword is used, the domain is not defined as a dimension of the user's partitioned table, and is therefore not copied across the nodes.