Reference Manual
CHAPTER 9. SQL Statements
To create a user-defined data type in the database.
CREATE { DOMAIN | DATATYPE } [ AS ] domain-name data-type
... [ [ NOT ] NULL ]
... [ DEFAULT default-value ]
... [ CHECK ( condition ) ]
domain-name: identifier
data-type: built-in data type, with precision and scale
Must have RESOURCE authority.
Automatic commit.
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.
It is recommended that you use CREATE DOMAIN, rather than CREATE DATATYPE, because CREATE DOMAIN is the ANSI/ISO SQL3 term.
User-defined data types can have CHECK conditions and DEFAULT conditions associated with them, and you can indicate whether the data type permits NULL values or not. These conditions are inherited by any column defined on the data type. Any conditions explicitly specified on the column override the data type conditions.
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. Any column created on a user-defined data type either allows or does not allow NULLs depending on the setting of the user-defined data type at the time the column was created, not on the current setting of the allow_nulls_by_default option. Any explicit setting of NULL or NOT NULL in the column definition overrides the user-defined data type setting.
When creating a CHECK condition, you can use a variable name prefixed with the @ sign in the condition. When the data type is used in the definition of a column, such a variable is replaced by the column name. This allows CHECK conditions to be defined on data types and used by columns of any name.
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.
SQL/92 Intermediate level feature.
Sybase Not supported by Adaptive Server Enterprise. Transact-SQL provides similar functionality using the CREATE DEFAULT and CREATE RULE statements.
The following statement creates a data type named address, which holds a 35-character string, and which may be NULL.
CREATE DOMAIN address CHAR( 35 ) NULL
The following statement creates a data type named id, which does not allow NULLS, and which is autoincremented by default.
CREATE DOMAIN id INT NOT NULL DEFAULT AUTOINCREMENT