15.00 - Column Comparisons - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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.