Firebird 2.5 Language ReferenceFirebird 2.5 Language ReferenceData Definition (DDL) Statements → DOMAIN
Firebird Firebird Prev: SHADOWFirebird 2.5 Language ReferenceUp: Data Definition (DDL) StatementsNext: TABLE

DOMAIN

Table of Contents

CREATE DOMAIN
ALTER DOMAIN
DROP DOMAIN

Domain is one of the object types in a relational database. A domain is created as a specific data type with some attributes attached to it. Once it has been defined in the database, it can be reused repeatedly to define table columns, PSQL arguments and PSQL local variables. Those objects inherit all of the attributes of the domain. Some attributes can be overriden when the new object is defined, if required.

This section describes the syntax of statements used to create, modify and delete domains. A detailed description of domains and their usage can be found in Custom Data Types—Domains.

CREATE DOMAIN

Used for: Creating a new domain

Available in: DSQL, ESQL

Syntax: 

CREATE DOMAIN name [AS] <datatype>
[DEFAULT {literal | NULL | <context_var>}]
[NOT NULL] [CHECK (<dom_condition>)]
[COLLATE collation_name];

<datatype> ::=
    {SMALLINT | INTEGER | BIGINT} [<array_dim>]
  | {FLOAT | DOUBLE PRECISION} [<array_dim>]
  | {DATE | TIME | TIMESTAMP} [<array_dim>]
  | {DECIMAL | NUMERIC} [(precision [, scale])] [<array_dim>]
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)]
    [<array_dim>] [CHARACTER SET charset_name]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING]
    [(size)] [<array_dim>]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset_name]
  | BLOB [(seglen [, subtype_num])]

<array_dim> ::= [[m:]n [,[m:]n ...]]

<dom_condition> ::=
    <val> <operator> <val>
  | <val> [NOT] BETWEEN <val> AND <val>
  | <val> [NOT] IN (<val> [, <val> ...] | <select_list>)
  | <val> IS [NOT] NULL
  | <val> IS [NOT] DISTINCT FROM <val>
  | <val> [NOT] CONTAINING <val>
  | <val> [NOT] STARTING [WITH] <val>
  | <val> [NOT] LIKE <val> [ESCAPE <val>]
  | <val> [NOT] SIMILAR TO <val> [ESCAPE <val>]
  | <val> <operator> {ALL | SOME | ANY} (<select_list>)
  | [NOT] EXISTS (<select_expr>)
  | [NOT] SINGULAR (<select_expr>)
  | (<dom_condition>)
  | NOT <dom_condition>
  | <dom_condition> OR <dom_condition>
  | <dom_condition> AND <dom_condition>

<operator> ::= 
  <> | != | ^= | ~= | = | < | > | <= | >= | !< | ^< | ~< | !> | ^> | ~>

<val> ::=
    VALUE
  | literal
  | <context_var>
  | <expression>
  | NULL
  | NEXT VALUE FOR genname
  | GEN_ID(genname, <val>)
  | CAST(<val> AS <datatype>)
  | (<select_one>)
  | func([<val> [, <val> ...]])
        

Table 5.5. CREATE DOMAIN Statement Parameters

Parameter Description
name Domain name consisting of up to 31 characters
datatype SQL data type
literal A literal value that is compatible with datatype
context_var Any context variable whose type is compatible with datatype
dom_condition Domain condition
collation_name Name of a collation sequence that is valid for charset_name, if it is supplied with datatype or, otherwise, is valid for the default character set of the database
array_dim Array dimensions
m, n INTEGER numbers defining the index range of an array dimension
precision The total number of significant digits that a value of the datatype can hold (1..18)
scale The number of digits after the decimal point (0..precision)
size The maximum size of a string in characters
charset_name The name of a valid character set, if the character set of the domain is to be different to the default character set of the database
subtype_num BLOB subtype number
subtype_name BLOB subtype mnemonic name
seglen Segment size (max. 65535)
select_one A scalar SELECT statement—selecting one column and returning only one row
select_list A SELECT statement selecting one column and returning zero or more rows
select_expr A SELECT statement selecting one or more columns and returning zero or more rows
expression An expression resolving to a value that is compatible with datatype
genname Sequence (generator) name
func Internal function or UDF


The CREATE DOMAIN statement creates a new domain.

Any SQL data type can be specified as the domain type.

Type-specific Details

ARRAY Types: 

  • If the domain is to be an array, the base type can be any SQL data type except BLOB and ARRAY.
  • The dimensions of the array are specified between square brackets. (In the Syntax block, these brackets appear in boldface to distinguish them from the square brackets that identify optional syntax elements.)
  • For each array dimension, one or two integer numbers define the lower and upper boundaries of its index range:
    • By default, arrays are 1-based. The lower boundary is implicit and only the upper boundary need be specified. A single number smaller than 1 defines the range num..1 and a number greater than 1 defines the range 1..num.
    • Two numbers separated by a colon (':') and optional whitespace, the second greater than the first, can be used to define the range explicitly. One or both boundaries can be less than zero, as long as the upper boundary is greater than the lower.
  • When the array has multiple dimensions, the range definitions for each dimension must be separated by commas and optional whitespace.
  • Subscripts are validated only if an array actually exists. It means that no error messages regarding invalid subscripts will be returned if selecting a specific element returns nothing or if an array field is NULL.

CHARACTER Types: You can use the CHARACTER SET clause to specify the character set for the CHAR, VARCHAR and BLOB (SUB_TYPE TEXT ) types. If the character set is not specified, the character set specified as DEFAULT CHARACTER SET in creating the database will be used. If no character set was specified then, the character set NONE is applied by default when you create a character domain.

[Warning] Warning

With character set NONE, character data are stored and retrieved the way they were submitted. Data in any encoding can be added to a column based on such a domain, but it is impossible to add this data to a column with a different encoding. Because no transliteration is performed between the source and destination encodings, errors may result.

DEFAULT Clause:  The optional DEFAULT clause allows you to specify a default value for the domain. This value will be added to the table column that inherits this domain when the INSERT statement is executed, if no value is specified for it in the DML statement. Local variables and arguments in PSQL modules that reference this domain will be initialized with the default value. For the default value, use a literal of a compatible type or a context variable of a compatible type.

NOT NULL Constraint: Columns and variables based on a domain with the NOT NULL constraint will be prevented from being written as NULL, i.e., a value is required.

[Caution] Caution

When creating a domain, take care to avoid specifying limitations that would contradict one another. For instance, NOT NULL and DEFAULT NULL are contradictory.

CHECK Constraint[s]The optional CHECK clause specifies constraints for the domain. A domain constraint specifies conditions that must be satisfied by the values of table columns or variables that inherit from the domain. A condition must be enclosed in parentheses. A condition is a logical expression (also called a predicate) that can return the Boolean results TRUE, FALSE and UNKNOWN. A condition is considered satisfied if the predicate returns the value TRUE or “unknown value” (equivalent to NULL). If the predicate returns FALSE, the condition for acceptance is not met.

VALUE Keyword: The keyword VALUE in a domain constraint substitutes for the table column that is based on this domain or for a variable in a PSQL module. It contains the value assigned to the variable or the table column. VALUE can be used anywhere in the CHECK constraint, though it is usually used in the left part of the condition.

COLLATE: The optional COLLATE clause allows you to specify the collation sequence if the domain is based on one of the string data types, including BLOBs with text subtypes. If no collation sequence is specified, the collation sequence will be the one that is default for the specified character set at the time the domain is created.

Any user connected to the database can create a domain.

CREATE DOMAIN Examples

  1. Creating a domain that can take values greater than 1,000, with a default value of 10,000.
    CREATE DOMAIN CUSTNO AS
    INTEGER DEFAULT 10000
    CHECK (VALUE > 1000);
                
  2. Creating a domain that can take the values 'Yes' and 'No' in the default character set specified during the creation of the database.
    CREATE DOMAIN D_BOOLEAN AS
    CHAR(3) CHECK (VALUE IN ('Yes', 'No'));
                
  3. Creating a domain with the UTF8 character set and the UNICODE_CI_AI collation sequence.
    CREATE DOMAIN FIRSTNAME AS
    VARCHAR(30) CHARACTER SET UTF8
    COLLATE UNICODE_CI_AI;
                
  4. Creating a domain of the DATE type that will not accept NULL and uses the current date as the default value.
    CREATE DOMAIN D_DATE AS
    DATE DEFAULT CURRENT_DATE
    NOT NULL;
                
  5. Creating a domain defined as an array of 2 elements of the NUMERIC(18, 3) type. The starting array index is 1.
    CREATE DOMAIN D_POINT AS
    NUMERIC(18, 3) [2];
                
    [Note] Note

    Domains defined over an array type may be used only to define table columns. You cannot use array domains to define local variables in PSQL modules.

  6. Creating a domain whose elements can be only country codes defined in the COUNTRY table.
    CREATE DOMAIN D_COUNTRYCODE AS CHAR(3)
    CHECK (EXISTS(SELECT * FROM COUNTRY
           WHERE COUNTRYCODE = VALUE));
                
    [Note] Note

    The example is given only to show the possibility of using predicates with queries in the domain test condition. It is not recommended to create this style of domain in practice unless the lookup table contains data that are never deleted.

See also: ALTER DOMAIN, DROP DOMAIN

ALTER DOMAIN

Used for: Altering the current attributes of a domain or renaming it

Available in: DSQL, ESQL

Syntax: 

ALTER DOMAIN domain_name
  [TO <new_name>]
  [TYPE <datatype>]
  [SET DEFAULT {literal | NULL | <context_var>} | DROP DEFAULT]
  [ADD [CONSTRAINT] CHECK (<dom_condition>) | DROP CONSTRAINT]

<datatype> ::=
    {SMALLINT | INTEGER | BIGINT}
  | {FLOAT | DOUBLE PRECISION}
  | {DATE | TIME | TIMESTAMP}
  | {DECIMAL | NUMERIC} [(precision [, scale])]
  | {CHAR | CHARACTER | CHARACTER VARYING | VARCHAR} [(size)]
    [CHARACTER SET charset_name]
  | {NCHAR | NATIONAL CHARACTER | NATIONAL CHAR} [VARYING] [(size)]
  | BLOB [SUB_TYPE {subtype_num | subtype_name}]
    [SEGMENT SIZE seglen] [CHARACTER SET charset_name]
  | BLOB [(seglen [, subtype_num])]

<dom_condition> ::=
    <val> <operator> <val>
  | <val> [NOT] BETWEEN <val> AND <val>
  | <val> [NOT] IN (<val> [, <val> ...] | <select_list>)
  | <val> IS [NOT] NULL
  | <val> IS [NOT] DISTINCT FROM <val>
  | <val> [NOT] CONTAINING <val>
  | <val> [NOT] STARTING [WITH] <val>
  | <val> [NOT] LIKE <val> [ESCAPE <val>]
  | <val> [NOT] SIMILAR TO <val> [ESCAPE <val>]
  | <val> <operator> {ALL | SOME | ANY} (<select_list>)
  | [NOT] EXISTS (<select_expr>)
  | [NOT] SINGULAR (<select_expr>)
  | (<dom_condition>)
  | NOT <dom_condition>
  | <dom_condition> OR <dom_condition>
  | <dom_condition> AND <dom_condition>

<operator> ::= 
  <> | != | ^= | ~= | = | < | > | <= | >= | !< | ^< | ~< | !> | ^> | ~>

<val> ::=
    VALUE
  | literal
  | <context_var>
  | <expression>
  | NULL
  | NEXT VALUE FOR genname
  | GEN_ID(genname, <val>)
  | CAST(<val> AS <datatype>)
  | (<select_one>)
  | func([<val> [, <val> ...]])
        

Table 5.6. ALTER DOMAIN Statement Parameters

Parameter Description
new_name New name for domain, consisting of up to 31 characters
datatype SQL data type
literal A literal value that is compatible with datatype
context_var Any context variable whose type is compatible with datatype
precision The total number of significant digits that a value of the datatype can hold (1..18)
scale The number of digits after the decimal point (0..precision)
size The maximum size of a string in characters
charset_name The name of a valid character set, if the character set of the domain is to be changed
subtype_num BLOB subtype number
subtype_name BLOB subtype mnemonic name
seglen Segment size (max. 65535)
select_one A scalar SELECT statement—selecting one column and returning only one row
select_list A SELECT statement selecting one column and returning zero or more rows
select_expr A SELECT statement selecting one or more columns and returning zero or more rows
expression An expression resolving to a value that is compatible with datatype
genname Sequence (generator) name
func Internal function or UDF


The ALTER DOMAIN statement enables changes to the current attributes of a domain, including its name. You can make any number of domain alterations in one ALTER DOMAIN statement.

TO <name>:  Use the TO clause to rename the domain, as long as there are no dependencies on the domain, i.e. table columns, local variables or procedure arguments referencing it.

SET DEFAULT:  With the SET DEFAULT clause you can set a new default value. If the domain already has a default value, there is no need to delete it first—it will be replaced by the new one.

DROP DEFAULT: Use this clause to delete a previously specified default value and replace it with NULL.

ADD CONSTRAINT CHECK:  Use the ADD CONSTRAINT CHECK clause to add a CHECK constraint to the domain. If the domain already has a CHECK constraint, it will have to be deleted first, using an ALTER DOMAIN statement that includes a DROP CONSTRAINT clause.

TYPE:  The TYPE clause is used to change the data type of the domain to a different, compatible one. The system will forbid any change to the type that could result in data loss. An example would be if the number of characters in the new type were smaller than in the existing type.

[Important] Important

When you alter the attributes of a domain, existing PSQL code may become invalid. For information on how to detect it, read the piece entitled The RDB$VALID_BLR Field in Appendix A.

Any user connected to the database can alter a domain, provided it is not prevented by dependencies from objects to which that user does not have sufficient privileges.

What ALTER DOMAIN Cannot Alter

  • If the domain was declared as an array, it is not possible to change its type or its dimensions; nor can any other type be changed to an ARRAY type.
  • In Firebird 2.5 and lower, the NOT NULL constraint can be neither enabled nor disabled for a domain.
  • There is no way to change the default collation without dropping the domain and recreating it with the desired attributes.

ALTER DOMAIN Examples

  1. Changing the data type to INTEGER and setting or changing the default value to 2,000:
    ALTER DOMAIN CUSTNO
      TYPE INTEGER
      SET DEFAULT 2000;
                
  2. Renaming a domain.
    ALTER DOMAIN D_BOOLEAN TO D_BOOL;
                
  3. Deleting the default value and adding a constraint for the domain:
    ALTER DOMAIN D_DATE
      DROP DEFAULT
      ADD CONSTRAINT CHECK (VALUE >= date '01.01.2000');
                
  4. Changing the CHECK constraint:
    ALTER DOMAIN D_DATE
      DROP CONSTRAINT;
    
    ALTER DOMAIN D_DATE
      ADD CONSTRAINT CHECK
        (VALUE BETWEEN date '01.01.1900' AND date '31.12.2100');
                
  5. Changing the data type to increase the permitted number of characters:
    ALTER DOMAIN FIRSTNAME
      TYPE VARCHAR(50) CHARACTER SET UTF8;
                

See also: CREATE DOMAIN, DROP DOMAIN

DROP DOMAIN

Used for: Deleting an existing domain

Available in: DSQL, ESQL

Syntax: 

DROP DOMAIN domain_name
        

The DROP DOMAIN statement deletes a domain that exists in the database. It is not possible to delete a domain if it is referenced by any database table columns or used in any PSQL module. In order to delete a domain that is in use, all columns in all tables that refer to the domain will have to be dropped and all references to the domain will have to be removed from PSQL modules.

Any user connected to the database can drop a domain.

Example

Deleting the COUNTRYNAME domain:

DROP DOMAIN COUNTRYNAME;
          

See also:  CREATE DOMAIN, ALTER DOMAIN

Prev: SHADOWFirebird 2.5 Language ReferenceUp: Data Definition (DDL) StatementsNext: TABLE
Firebird 2.5 Language ReferenceFirebird 2.5 Language ReferenceData Definition (DDL) Statements → DOMAIN