FirebirdSQL logo
 SHADOWTABLE 

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.

  • 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.

String 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 of the database will be used.If the database has no default character set, the character set NONE is applied by default when you create a character domain.

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

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 if the domain is based on one of the string data types, including BLOBs with text subtypes.If no collation is specified, the collation will be the one that is default for the specified character set at the time the domain is created.

Who Can Create a Domain

The CREATE DOMAIN statement can be executed by: