ASA SQL User's Guide
  Ensuring Data Integrity
    Using domains

Creating domains (SQL)

You can use the CREATE DOMAIN statement to create and define domains.

To create a new domain (SQL)

1. Connect to a database.

2. Execute a CREATE DOMAIN statement.

Example 1: Simple domains 

Some columns in the database are to be used for people's names and others are to store addresses. You might then define type following domains.

CREATE DOMAIN persons_name CHAR(30)
CREATE DOMAIN street_address CHAR(35)

Having defined these domains, you can use them much as you would the built-in data types. For example, you can use these definitions to define a tables as follows.

CREATE TABLE customer (
   id INT  DEFAULT AUTOINCREMENT  PRIMARY KEY
   name persons_name
   address street_address
)

Example 2: Default values, check constraints, and identifiers 

In the above example, the table's primary key is specified to be of type integer. Indeed, many of your tables may require similar identifiers. Instead of specifying that these are integers, it is much more convenient to create an identifier domain for use in these applications.

When you create a domain, you can specify a default value and provide check constraint to ensure that no inappropriate values are typed into any column of this type.

Integer values are commonly used as table identifiers. A good choice for unique identifiers is to use positive integers. Since such identifiers are likely to be used in many tables, you could define the following domain.

CREATE DOMAIN identifier INT
DEFAULT AUTOINCREMENT
CHECK ( @col > 0 )

This check constraint uses the variable @col. Using this definition, you can rewrite the definition of the customer table, shown above.

CREATE TABLE customer (
   id identifier PRIMARY KEY
   name persons_name
   address street_address
)

Example 3: Built-in domains 

Adaptive Server Anywhere comes with some domains pre-defined. You can use these pre-defined domains as you would a domain that you created yourself. For example, the following monetary domain has already been created for you.

CREATE DOMAIN MONEY NUMERIC(19,4)
NULL

For more information, see CREATE DOMAIN statement.