15.00 - Domains - Teradata Database

Teradata Database Design

Teradata Database
User Guide


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” on page 132).

Note: 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.