Creating a Domain
Go Up to Creating Metadata
CREATE DOMAIN creates a column definition that is global to the database, and that can be used to define columns in subsequent CREATE TABLE statements. CREATE DOMAIN is especially useful when many tables in a database contain identical column definitions. For example, in an employee database, several tables might define columns for employees’ first and last names.
At its simplest, the syntax for CREATE DOMAIN is:
EXEC SQL CREATE DOMAIN name AS <data_type>;
The following statements create two domains, FIRSTNAME, and LASTNAME.
EXEC SQL CREATE DOMAIN FIRSTNAME AS VARCHAR(15); EXEC SQL CREATE DOMAIN LASTNAME AS VARCHAR(20); EXEC SQL COMMIT;
Once a domain is defined and committed, it can be used in CREATE TABLE statements to define columns. For example, the following CREATE TABLE fragment illustrates how the FIRSTNAME and LASTNAME domains can be used in place of column definitions in the EMPLOYEE table definition.
EXEC SQL CREATE TABLE EMPLOYEE ( . . . FIRST_NAME FIRSTNAME NOT NULL, LAST_NAME LASTNAME NOT NULL; . . . );
A domain definition can also specify a default value, a NOT NULL attribute, a CHECK constraint that limits inserts and updates to a range of values, a character set, and a collation order.
For more information about creating domains and using them during table creation, see the Data Definition Guide. For the complete syntax of CREATE DOMAIN, see the Language Reference Guide.