CREATE DOMAIN
Creates
a new domain.
SYNTAX
| domain_name |
Name
of the domain that you want to create. |
| data_type |
Data
type to use for the domain. |
| constant |
Constant
value to use if no value is inserted. |
| function_name |
Built-in
function to use if no value is inserted. |
| boolean_expression |
Any
expression that evaluates to true or false. |
DESCRIPTION
The CREATE
DOMAIN command creates a new domain with an optional default value and
optional integrity constraints. Any user with RESOURCE or higher security
privileges can execute the CREATE DOMAIN command.
A domain
is a user-defined data type that brings together a data type, a default
value, and a value constraint. You can use a domain in the column definition
of CREATE TABLE or ALTER TABLE ADD COLUMN statements in place of a data
type to define the set of valid values that can be entered into the column.
For example,
you can create a domain based on the DATE data type with a default value
of NOW() that only accepts dates between January 1st, 1900 and today.
Any column you create using this domain will inherit these characteristics,
allowing you to provide a consistent definition for columns that contain
the same type of data without explicitly specifying default values and
value constraints each time.
When you
create a domain, you must specify the data type, and can optionally specify
a default value and a value constraint. You may use any data type DBMaker
supports when creating a domain, except the SERIAL data type. You specify
default values and value constraints using the DEFAULT and CHECK keywords.
The DEFAULT
keyword is optional. This keyword is used to specify a default value that
will be inserted into a column if no value is provided when inserting
a new row. Constants, results from built-in functions, or the NULL keyword
may be used as the default value. You can only use built-in functions
that have no argument, such as PI(), NOW(), or USER(), when creating a
domain. If you use the NULL keyword as the DEFAULT value, the column cannot
be defined with the NOT NULL keyword.
The CHECK
keyword is optional. This keyword is used to specify a range of acceptable
values (constraints) that may be entered in a column. The expression that
specifies the range of acceptable values may be any expression that evaluates
to true or false. The VALUE keyword may be used in the expression in conjunction
with the CHECK keyword to represent the value of the column. If an SQL
statement does not satisfy the CHECK conditions, it will not be processed.
Specifying
the default values and value constraints by using domains gives the same
results as specifying them in a standard column definition. However, any
default values provided in the column definition will override the default
value of the domain, and the column definition can also add value constraints
in addition to those of the domain.
You should
ensure the value constraints specified explicitly in a column definition
do not conflict with the value constraints provided by the domain. DBMaker
does not check for conflicting constraints when creating a column based
on a domain, but the conflicting constraints may prevent you from inserting
or updating some or all of the data in the table.
Domain names
have a maximum length of eighteen characters, and may contain numbers,
letters, the underscore character, and the symbols $ and #. The first
character may not be a number.
EXAMPLE
The following
example creates a domain named AllNum based on the INTEGER data type.
CREATE DOMAIN AllNum AS INTEGER
|
The following
example creates a domain named AllNum based on the INTEGER data type which
has a default value of 0.
CREATE DOMAIN AllNum AS INTEGER DEFAULT 0
|
The following
example creates a domain named AllNum based on the INTEGER data type which
does not allow NULL values.
CREATE DOMAIN AllNum AS INTEGER CHECK VALUE IS NOT NULL
|
The following
example creates a domain named PosNum based on the INTEGER data type which
only allows integer values between 0 and 100, and has a default value
of 0.
CREATE DOMAIN PosNum AS INTEGER DEFAULT 0 CHECK VALUE >= 0
|
The following
example creates a domain named ValidDate based on the DATE data type which
uses the NOW() function as both the default value and one of the value
constraints. (Note that only functions that do not take an argument may
be used when creating domains.)
CREATE DOMAIN ValidDate AS DATE
DEFAULT NOW()
CHECK VALUE > '01/01/1900' AND VALUE <= NOW()
|
RELATED COMMANDS
<
CREATE DB | Contents
| CREATE GROUP >
|
