Sybase SQL Anywhere - CREATE DOMAIN - Create User-Defined Data Type (UDT)

CREATE DOMAIN statement creates a user-defined data type with a range, optional DEFAULT, NOT NULL and CHECK constraint. A domain is an alias for a built-in data type.

Quick Example:

   -- Define a type
   CREATE DOMAIN addr VARCHAR(90);
 
   -- Use it in a table
   CREATE TABLE location (address addr);
 
   -- Insert data into user-defined type (no changes in INSERT syntax)
   INSERT INTO location VALUES ('Kansas City');

CREATE DOMAIN Overview

Summary information:

Syntax (full...)	CREATE DOMAIN \| DATATYPE name [AS] datatype [constraints]
DEFAULT Value
NOT NULL and NULL		Default is NULL, can be changed using allow_nulls_by_default option
CHECK Constraint		@var references checked column (var is any valid identifier)
Composite Type		Only single field
Naming	Columns and domain types can have the same name
Transactions	Automatic COMMIT is performed after CREATE DOMAIN statement

Last Update: Sybase SQL Anywhere 12.0

CREATE DOMAIN Details

CREATE DOMAIN statement allows you to create an alias for a built-in data type, and assign range and value constraints:

   CREATE DOMAIN addr VARCHAR(90) NOT NULL DEFAULT 'N/A';
 
   CREATE DOMAIN areacode INT CHECK (@code > 200 AND @code < 999);

Let's create a sample table using the created domain types:

   CREATE TABLE location
   (
     address addr,
     area_code areacode 
   );

Note that areacode domain contains @code identifier in the CHECK constraint that will be replaced by area_code column name when the constraint is checked.

Let's insert data:

   INSERT INTO location VALUES('Kansas City, MO', 816);
   -- 1 row(s) inserted
 
   -- Insert default address, and area code NULL
   INSERT INTO location (area_code) VALUES(NULL);
   -- 1 row(s) inserted
 
   -- Insert address, and area code that is out of range
   INSERT INTO location VALUES('Place', 101);
   -- Error: Constraint 'ASA6464' violated: Invalid value for column 'area_code' in table 'location'
   -- SQLCODE=-209, ODBC 3 State="23000"

Note that CHECK constraint does not prevent from inserting NULL values, it is applied to not-NULL values only.

Table content:

Kansas City, MO	816
N/A	NULL

Create ID Type (Auto-increment or Identity)

Domains also allow you creating an ID type:

   CREATE DOMAIN id INT NOT NULL DEFAULT AUTOINCREMENT;

Then you can use ID in a table as the identity (auto-increment) column:

   CREATE TABLE countries
   (
      id id, 
      name VARCHAR(70)
   );
 
   -- Specify country name only, id will be automatically assigned
   INSERT INTO countries (name) VALUES ('Czech Republic');
   INSERT INTO countries (name) VALUES ('United States');