15.00 - Column-Level Constraints - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Column-Level Constraints

Column-level constraints define more elaborate integrity rules for columns than those defined by simple domain constraints. For example, a column constraint on a column typed as INTEGER might further specify that only values between 0 and 49999 or between 99995 and 99999 are permitted for that column.

A column constraint specifies a simple predicate that applies to one column only. For example, if some business rule states that employee numbers in the employee table must be between 10001 and 32001 inclusive. You could specify this rule in the CREATE TABLE statement used to define the emp_no column in the employee table as follows:

   CONSTRAINT emp_no CHECK (emp_no >= 10001 AND emp_no <= 32001)

Notice that the only column referred to by this constraint is the employee number column, emp_no.

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

You cannot declare column‑level CHECK constraints on any column defined with the XML, JSON, BLOB, CLOB, BLOB‑based UDT, CLOB‑based UDT, XML‑based UDT,
ARRAY/VARRAY, Period, or Geospatial data types.

You can declare CHECK and UNIQUE constraints on row‑level security constraint columns. When you define these constraints for a column, they apply to all rows in the table, not just to rows that are user‑visible. You can also declare UNIQUE constraints on distinct and structured UDT columns as long as they are not based on XML, JSON, BLOB, or CLOB data.

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

You can also define column‑level and object‑level access constraints, or security privileges, on tables (for details, see Security Administration and SQL Data Control Language).