15.00 - Table-Level Constraints - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Table-Level Constraints

Table-level constraints specify sets of relationships among values within a row. A table-level constraint can be either single‑row or multirow, and applies to table columns rather than table rows.

For example, you might specify a table constraint on the flight_reservations table (see “An Airline Reservation System” on page 631) that requires the value for the scheduled arrival date, a_date, to be less than or equal to the value for the scheduled departure date, d_date. This is a single-row table constraint.

The SQL definition for this constraint looks like this:

     CONSTRAINT arrive_date_check CHECK (a_date >= d_date)

Notice that this constraint refers to two columns, a_date and d_date; therefore, must be a table constraint.

You probably want to ensure that the value for res_num is unique and non‑null, so you would define a uniqueness constraint on the table for reservation numbers. In this case, res_num is the primary key for the flight_reservations table, so the constraint is called a primary key constraint.

The SQL definition looks like this.

     res_num INTEGER NOT NULL CONSTRAINT pKey PRIMARY KEY

Primary key constraints are a subset of uniqueness constraints: all primary keys are also unique, but all unique columns are not primary keys because a relation can only have one primary key. Because any unique column set is, by definition, also a candidate key, uniqueness constraints are sometimes referred to as key constraints. The primary key constraint on res_num is also a multirow table constraint because it enforces the rule that every row in the table has at least one unique value: its reservation number.

Suppose reservation number is not the primary key for the flight_reservations table, but your business rules require the values for res_num to be unique. To do this, you write a constraint that enforces uniqueness on the res_num column. The SQL definition looks like this:

     res_num INTEGER NOT NULL CONSTRAINT ResNumUnique UNIQUE

The table constraints for your databases are developed from the ATM Constraints form (see “Constraints Form” on page 142).

You cannot declare table‑level CHECK constraints on any column defined with XML, BLOB, CLOB, UDT, Period, or JSON data types.

You cannot specify constraints other than NULL or NOT NULL for global temporary trace tables. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language.