16.10 - Domains - Teradata Database

Teradata Database Design

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
User Guide
Publication ID
B035-1094-161K
Language
English (United States)

The concept of domain as used in relational database theory derives directly from the more formal definition given in set theory and function theory. A variable is defined as a set of points having both a domain and a range, where the domain defines the specific type of data represented by the set and the range defines the bounds on that type.

A more concise way of expressing this is to say that domains are data types. It is frequently a good idea to extend this definition to include constraints defined for a column. Extended domains of this type are frequently referred to as business rules. A simple example would be to define employee_number as an INTEGER type and extend the definition with a constraint to disallow negative integer numbers as valid employee numbers. Such an example conflates the mathematical notions of range and domain. Strictly speaking, a domain is only a data type, and range constraints are not part of its definition.

Domains have been a fundamental concept supporting the theory of relational databases since the inception of that theory. The variables described by a domain in a relational database are the valid values an attribute, or column, can have.

Unfortunately, the ANSI/ISO SQL standard does not support a rigorous atomic definition of domain, so it is up to the database designer to define the domains for a database and their ranges rigorously by creating various constraints on table columns, by creating appropriate user-defined data types (the behaviors of distinct UDTs are based on the behaviors of the predefined data types from which they are derived. Distinct UDTs can optionally also have user-defined behaviors.

The behaviors of the other category of user-defined data types, structured UDTs, are exclusively user-defined. See SQL Data Definition Language for more information about creating distinct and structured UDTs and SQL Data Definition Language and SQL External Routine Programming for details about user-defined data types and their associated database objects), and by ensuring that applications do not make nonsensical cross-domain data manipulations such as subtracting an integer part number from an integer inventory count (see Column Comparisons).

You cannot specify a referential integrity constraint for a UDT or CHECK constraint column, though UDT columns (with the exception of ARRAY, VARRAY, Period, XML, JSON, and Geospatial types) can be used in the definition of a UNIQUE or PRIMARY KEY constraint or a primary or secondary index. You can otherwise use various constraints, particularly CHECK constraints, to restrict the range of values a column will accept, so if your application requires any kind of check, uniqueness, or referential constraints, you cannot define a domain for the column using a distinct type.

Properties of Domains In Teradata Database

Domains in Teradata Database are defined to have the following properties:
  • A domain defines the set of possible values that can be written to a table column.
  • The values of a domain cannot be decomposed to component values; they are atomic.
    This is not true for domains having INTERVAL, TIMESTAMP, or structured UDT data types, which are not atomic, but the concept generally holds true otherwise.
  • A domain must have a name.
  • A domain must have an assigned data type.

Domains and Keys

The following rules apply to these relationships.
  • All primary key values for a table are drawn from the domain that defines all possible values for that column.

    That domain cannot be defined with an XML, BLOB, or CLOB data type.

  • All foreign key values that reference a primary key value in another table are drawn from the same domain as the values for that primary key.

    That domain cannot be defined with an XML, BLOB, or CLOB data type.

Teradata Database Data Types

When you applications require a strict domain type to ensure domain integrity, particularly for column comparisons and arithmetic operations, you should consider defining user-defined distinct data types for those domains.

See SQL Data Definition Language for information about creating user-defined data types and their associated database objects.

See SQL External Routine Programming for information about writing external code for the methods you define to work with user-defined types.

The following table lists the valid Teradata Database predefined data types you can use to define a domain or to create new user-defined data types:

Data Type Definition
  • ARRAY
  • VARRAY
Not valid.

You cannot create a distinct UDT using a ARRAY/VARRAY data types, so you cannot create a domain using an ARRAY/VARRAY predefined type as its base.

  • BLOB
  • BYTE
  • VARBYTE
A binary integer used to store digital images.
BINARY LARGE OBJECT (BLOB) A large binary string used to store binary objects such as musical recordings, videos, and other multimedia.
  • CLOB
  • CHARACTER
  • VARCHAR
  • LONG VARCHAR
  • GRAPHIC
Any glyph from a supported language.

For the English language, this type is often referred to as alphanumeric.

The CLOB type is typically used to define large character objects whose length exceeds 64KB.

CHARACTER LARGE OBJECT (CLOB) A large character string used to store documents, possibly encoded using tag languages such as XML.
XML A large binary string used to store XML documents in XML format.
DATE A valid, named 24-hour epoch from the Gregorian calendar.
DECIMAL Any base-10 real number, including those with a fractional part.
NUMBER (Exact form) Any base-10 real number, including those with a fractional part.
  • FLOAT
  • REAL
  • DOUBLE PRECISION
A rational number expressed in exponential format which, depending on the value, might be exact or might be an approximation to a real number.
NUMBER (Approximate form) Any base-10 rational number expressed in exponential format.
  • BIGINT
  • INTEGER
  • BYTEINT
  • SMALLINT
Any natural number.
INTERVAL A time duration with optional fractional precision. Interval data is not implemented atomically, though it is generally treated logically as if it were atomic.
TIME A valid time expressed using 24-hour notation with optional fractional precision.

The TIME type can also be defined with a TIME ZONE.

TIMESTAMP A valid date and time expressed using 24-hour notation with optional fractional precision.

The TIMESTAMP type can also be defined with a TIME ZONE.

Period Not valid.

You cannot create a distinct UDT using the Period data type, so you cannot create a domain using the Period predefined type as its base.

See SQL Data Types and Literals for complete listings and descriptions of the data types available for Teradata Database table columns.

You can also use these predefined data types to create your own user-defined data types that may be more suitable for your particular application workloads. See SQL Data Definition Language and SQL External Routine Programming for details.

Domains in the Logical Data Model

You should always define and assign domains to your table attributes during the logical design phase of your database design.

Domains can be bounded specifically, nonspecifically, or both simultaneously. For example, you can define the employee number domain to be both not null (a nonspecific boundary) and constrained within a restricted range such as greater than 100 and less than 1 000 000. Unfortunately, you cannot specify CHECK or any other types of constraints on UDT columns, which lessens their usefulness for defining domains.

Column Comparisons

The necessity for naming your domains becomes clear when you examine the topic of column comparisons.

According to the relational model, columns can be compared if and only if their values are drawn from the same domain.You cannot compare XML, BLOB, or CLOB columns using the built-in SQL operator set. You can write UDFs to make such comparisons, however, and if you create UDTs based on BLOB or CLOB types, you can create methods for those UDTs that would permit you can make such comparisons (see SQL External Routine Programming and SQL Data Definition Language for further information). If an application is to compare data from different columns in any way, all of the following statements must be true for all of the columns being compared.
  • They must share the same set of values.
  • The same value represents the same real world object in all cases.
  • The values can be compared, added, subtracted, and joined.

    Note that you cannot operate on XML, BLOB, or CLOB data types

In terms of domains, these rules can be stated as follows.
  • Columns from the same domain always have the same defined domain name, which can be based on a user-defined data type.
  • Columns from the same domain always have the same defined constraints.
  • Columns from the same domain always have the same data type.

    Particularly in the case of user-defined data types, the domain and the data type are often isomorphic.

Commercial relational database management systems relax these comparison restrictions to a greater or lesser degree. For example, you can compare INTEGER and DECIMAL values in commercially available systems because, the reasoning goes, both are numeric types.

Database management systems usually provide internal data type conversion routines to ensure such comparisons can be made. Programming languages generally refer to this as weak typing. The more strict domain comparison rules of the relational model, which are more strongly typed, do not permit these types of comparison to be made. UDTs are strongly typed and do not permit careless comparisons unless you write casts specifically to permit them. See CREATE CAST in SQL Data Definition Language for more information about creating cast functionality for UDTs.