Creates a user-defined data type in the database.
CREATE { DOMAIN | DATATYPE } domain-name data-type
… [ NOT ] NULL ]
… [ DEFAULT default-value ]
identifier
built-in data type, with precision and scale
special-value | string | global variable | [ - ] number | ( constant-expression ) | built-in-function( constant-expression ) | AUTOINCREMENT | CURRENT DATABASE | CURRENT REMOTE USER | NULL | TIMESTAMP | LAST USER
CURRENT { DATE | TIME | TIMESTAMP | USER | PUBLISHER } | USER
This statement creates a data type named address, which holds a 35-character string, and which may be NULL:
CREATE DOMAIN address CHAR( 35 ) NULL
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 “Using column defaults” in Chapter 9, “Ensuring Data Integrity” in the System Administration Guide: Volume 1.
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.
Automatic commit.
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.
Must have RESOURCE authority.
Chapter 3, “SQL Data Types” in Reference: Building Blocks, Tables, and Procedures
