CREATE DOMAIN
Go Up to Statement and Function Reference (Language Reference Guide)
Creates a column definition that is global to the database. Available in gpre, DSQL, and isql.
CREATE DOMAIN <domain> [AS] data_type
[DEFAULT {<literal> | NULL | USER}]
[NOT NULL] [CHECK (dom_search_condition)]
[COLLATE <collation>];
data_type> =
{SMALLINT|INTEGER|FLOAT|DOUBLE PRECISION} [array_dim]
| {DATE|TIME|TIMESTAMP} [array_dim]
| {DECIMAL | NUMERIC} [(precision [, scale])] [array_dim]
| {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(int)]
[array_dim] [CHARACTER SET charname]
| {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR}
[VARYING] [(int)] [array_dim]
| BLOB [SUB_TYPE {int | subtype_name}] [SEGMENT SIZE int]
[CHARACTER SET charname]
| BLOB [(seglen [, subtype])]
| BOOLEAN
array_dim> = [[x:]y [, [x:]y …]]
dom_search_condition> =
VALUE operator value
| VALUE [NOT] BETWEEN value AND value
| VALUE [NOT] LIKE value [ESCAPE value]
| VALUE [NOT] IN (value [, value …])
| VALUE IS [NOT] NULL
| VALUE [NOT] CONTAINING value
| VALUE [NOT] STARTING [WITH] value
| (dom_search_condition)
| NOT dom_search_condition
| dom_search_condition OR dom_search_condition
| dom_search_condition AND dom_search_condition
operator> = {= | < | > | <= | >= | !< | !> | <> | !=}
Note on the CREATE DOMAIN syntax:
COLLATEis useful only for text data, not for numeric types. Also, you cannot specify aCOLLATEclause for Blob columns.- When declaring arrays, you must include the outermost brackets, shown below in bold. For example, the following statement creates a 5 by 5 two-dimensional array of strings, each of which is six characters long:
my_array = varchar(6)[5,5]
- Use the colon (:) to specify an array with a starting point other than 1. The following example creates an array of
integervalues that begins at 20 and ends at 30:
my_array = integer[20:30]
In SQL statements passed to DSQL, omit the terminating semicolon. In embedded applications written in C and C++, and in
isql, the semicolon is a terminating symbol for the statement, so it must be included.| Argument | Description |
|---|---|
|
<domain> |
Unique name for the domain. |
|
<data_type> |
SQL data type |
|
|
Specifies a default column value that is entered when no other entry is made; possible values are: <literal> – Inserts a specified string, numeric value, or date value.
|
|
|
Specifies that the values entered in a column cannot be |
|
|
Creates a single |
|
|
Placeholder for the name of a column eventually based on the domain. |
|
|
Specifies a collation sequence for the domain. |
Description: CREATE DOMAIN builds an inheritable column definition that acts as a template for columns defined with CREATE TABLE or ALTER TABLE. The domain definition contains a set of characteristics, which include:
- Data type
- An optional default value
- Optional disallowing of
NULLvalues - An optional
CHECKconstraint - An optional collation clause
The CHECK constraint in a domain definition sets a dom_search_condition that must be true for data entered into columns based on the domain. The CHECK constraint cannot reference any domain or column.
Be careful not to create a domain with contradictory constraints, such as declaring a domain NOT NULL and assigning it a DEFAULT value of NULL.
The data type specification for a CHAR or VARCHAR text domain definition can include a CHARACTER SET clause to specify a character set for the domain. Otherwise, the domain uses the default database character set. For a complete list of character sets recognized by InterBase, see Character Sets and Collation Orders.
If you do not specify a default character set, the character set defaults to NONE. Using character set NONE means that there is no character set assumption for columns; data is stored and retrieved just as you originally entered it. You can load any character set into a column defined with NONE, but you cannot load that same data into another column that has been defined with a different character set. In these cases, no transliteration is performed between the source and destination character sets, so errors can occur during assignment.
The COLLATE clause enables specification of a particular collation order for CHAR, VARCHAR, and NCHAR text data types. Choice of collation order is restricted to those supported for the domain’s given character set, which is either the default character set for the entire database, or a different set defined in the CHARACTER SET clause as part of the data type definition. For a complete list of collation orders recognized by InterBase, see Character Sets and Collation Orders.
Columns based on a domain definition inherit all characteristics of the domain. The domain default, collation clause, and NOT NULL setting can be overridden when defining a column based on a domain. A column based on a domain can add additional CHECK constraints to the domain CHECK constraint.
Examples: The following isql statement creates a domain that must have a positive value greater than 1,000, with a default value of 9,999. The keyword VALUE substitutes for the name of a column based on this domain.
CREATE DOMAIN CUSTNO
AS INTEGER
DEFAULT 9999
CHECK (VALUE > 1000);
The next isql statement limits the values entered in the domain to four specific values:
CREATE DOMAIN PRODTYPE
AS VARCHAR(12)
CHECK (VALUE IN ('software', 'hardware', 'other', 'N/A'));
The following isql statement creates a domain that defines an array of CHAR data type:
CREATE DOMAIN DEPTARRAY AS CHAR(67) [4:5];
In the following isql example, the first statement creates a domain with USER as the default. The next statement creates a table that includes a column, ENTERED_BY, based on the USERNAME domain.
CREATE DOMAIN USERNAME AS VARCHAR(20)
DEFAULT USER;
CREATE TABLE ORDERS (ORDER_DATE DATE, ENTERED_BY USERNAME,
ORDER_AMT DECIMAL(8,2));
INSERT INTO ORDERS (ORDER_DATE, ORDER_AMT)
VALUES ('1-MAY-93', 512.36);
The INSERT statement does not include a value for the ENTERED_BY column, so InterBase automatically inserts the user name of the current user, JSMITH:
SELECT * FROM ORDERS;
1-MAY-93 JSMITH 512.36
The next isql statement creates a BLOB domain with a TEXT subtype that has an assigned character set:
CREATE DOMAIN DESCRIPT AS
BLOB SUB_TYPE TEXT SEGMENT SIZE 80
CHARACTER SET SJIS;