DOMAINS System Table Description

Description

SQL domains support a type of abstraction that can be referenced in many SQL tables. A change to the domain causes a corresponding change in the tables. SYSTEM.DOMAINS lists information about the SQL domains defined for servers that host a database.

A SQL domain is a feature of the standard SQL language. This is not to be confused with NuoDB domains. See definitions in the Glossary for domain (NuoDB) and domain (SQL).

Fields

Field Type Description

Field	Type	Description
DOMAINNAME	string	Name of the SQL domain
SCHEMA	string	Schema for the SQL domain
COLLATIONSEQUENCE	string	Collation associated with the SQL domain
DATATYPE	integer	Integer from `ID` in `SYSTEM.DATATYPES` for the data type assigned to the domain (see DATATYPES System Table Description).
LENGTH	integer	Length associated with the data type - this is the precision assigned to a `CHAR` or `BINARY` type or the size in bytes of the type
SCALE	integer	The scale specified for a fixed precision numeric type
PRECISION	integer	The precision specified for a fixed precision numeric, `CHAR` or `BINARY` type; the max precision possible for other numeric types
FLAGS	integer	Flags specifying additional info about this type of this SQL domain: 1 = column is `NOT NULL` 2 = reserved for future use to flag `Searchable` 4 = reserved for future use to flag `Case Insensitive` 8 = column is defined as `GENERATED ALWAYS AS IDENTITY`, as opposed to `GENERATED BY DEFAULT AS IDENTITY` (currently unsupported)
DEFAULTVALUE	string	A default value given at the time of `CREATE DOMAIN`
REMARKS	string	Unused
DECLARED_TYPE	string	The actual string for the type parsed from the `CREATE DOMAIN` command

DOMAINNAME

string

Name of the SQL domain

SCHEMA

string

Schema for the SQL domain

COLLATIONSEQUENCE

string

Collation associated with the SQL domain

DATATYPE

integer

Integer from ID in SYSTEM.DATATYPES for the data type assigned to the domain (see DATATYPES System Table Description).

LENGTH

integer

Length associated with the data type - this is the precision assigned to a CHAR or BINARY type or the size in bytes of the type

SCALE

integer

The scale specified for a fixed precision numeric type

PRECISION

integer

The precision specified for a fixed precision numeric, CHAR or BINARY type; the max precision possible for other numeric types

FLAGS

integer

Flags specifying additional info about this type of this SQL domain:

1 = column is NOT NULL
2 = reserved for future use to flag Searchable
4 = reserved for future use to flag Case Insensitive
8 = column is defined as GENERATED ALWAYS AS IDENTITY, as opposed to GENERATED BY DEFAULT AS IDENTITY (currently unsupported)

DEFAULTVALUE

string

A default value given at the time of CREATE DOMAIN

REMARKS

string

Unused

DECLARED_TYPE

string

The actual string for the type parsed from the CREATE DOMAIN command

Indexes

Primary Index: DOMAINS..PRIMARY_KEY on fields: SCHEMA, DOMAINNAME

Example

In the following example we create a domain called ZIPCODE, which is the same as a CHAR(5). We use “SET OUTPUT VERTICAL” because it is more readable if displayed this way (see SET).

           CREATE DOMAIN zipcode CHAR(5);
SET OUTPUT VERTICAL;
SELECT * FROM system.domains;
==================================== Row #1 ====================================
DOMAINNAME: ZIPCODE
SCHEMA: USER
COLLATIONSEQUENCE: <null>
DATATYPE: 2
LENGTH: 5
SCALE: 0
PRECISION: 5
FLAGS: 0
DEFAULTVALUE:
REMARKS: <null>
DECLARED_TYPE: char(5)