CREATE DOMAIN Statement

Creates a user-defined data type in the database.

Syntax

CREATE { DOMAIN | DATATYPE } domain-name data-type
… [ NOT ] NULL ]
… [ DEFAULT default-value ]

Parameters

domain-name: – identifier
data-type: – built-in data type, with precision and scale
default-value: – special-value | string | global variable | [ - ] number | ( constant-expression ) | built-in-function( constant-expression ) | AUTOINCREMENT | CURRENT DATABASE | CURRENT REMOTE USER | NULL | TIMESTAMP | LAST USER
special-value: – CURRENT { DATE | TIME | TIMESTAMP | USER | PUBLISHER } | USER

Examples

Example 1 – Create a data type named address, which holds a 35-character string, and which may be NULL:
```
CREATE DOMAIN address CHAR( 35 ) NULL
```

Usage

User-defined data types are aliases for built-in data types, including precision and scale values, where applicable. They improve convenience and encourage consistency in the database.

Sybase recommends that you use CREATE DOMAIN, rather than CREATE DATATYPE, as CREATE DOMAIN is the ANSI/ISO SQL3 term.

The user who creates a data type is automatically made the owner of that data type. No owner can be specified in the CREATE DATATYPE statement. The user-defined data type name must be unique, and all users can access the data type without using the owner as prefix.

User-defined data types are objects within the database. Their names must conform to the rules for identifiers. User-defined data type names are always case-insensitive, as are built-in data type names.

By default, user-defined data types allow NULLs unless the allow_nulls_by_default option is set to OFF. In this case, new user-defined data types by default do not allow NULLs. The nullability of a column created on a user-defined data type depends on the setting of the definition of the user-defined data type, not on the setting of the allow_nulls_by_default option when the column is referenced. Any explicit setting of NULL or NOT NULL in the column definition overrides the user-defined data type setting.

The CREATE DOMAIN statement allows you to specify DEFAULT values on user-defined data types. The DEFAULT value specification is inherited by any column defined on the data type. Any DEFAULT value explicitly specified on the column overrides that specified for the data type. For more information on the use of column DEFAULT values, see System Administration Guide: Volume 1 > Data Integrity > Column Defaults Encourage Data Integrity.

The CREATE DOMAIN statement lets you incorporate a rule, called a CHECK condition, into the definition of a user-defined data type.

Sybase IQ enforces CHECK constraints for base, global temporary. local temporary tables, and user-defined data types.

To drop the data type from the database, use the DROP statement. You must be either the owner of the data type or have DBA authority in order to drop a user-defined data type.

See also Reference: Building Blocks, Tables, and Procedures > SQL Data Types.

Side effects:

Automatic commit

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—Not supported by Adaptive Server Enterprise. Transact-SQL provides similar functionality using the sp_addtype system procedure and the CREATE DEFAULT and CREATE RULE statements.

Permissions

Must have RESOURCE authority.