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.

Note: 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)A set of peers. and domain (SQL)A standard user-defined field type in SQL. For example, define a domain named MONEY as NUMERIC(15,2). You can then define fields as being of type MONEY..

Fields

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

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)