- Domain
- Column
- Table
- Database
These four types of constraints are implemented in distinct ways in Teradata database, though the differences between column and table constraints are subtle.
Domain Constraints
Fundamentally, a domain is a data type. Data types act as simple constraints by not allowing you to, for example, insert a 20 character string into a field typed INTEGER.
The domain constraints for your databases are developed from the ATM Domains form. For more information, see Constraints Form.
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. For more information, see Constraints Form.
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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
You can also define column-level and object-level access constraints, or security privileges, on tables (for more information, see Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100 and Teradata Vantage™ - SQL Data Control Language, B035-1149).
Row-Level Security Constraints
You can define row-level security constraints on tables. Row-level security is not a semantic constraint, but row-level security constraints and semantic constraints may interact. For details about row-level security, see Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100.
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) 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. For more information, see Constraints Form.
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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
Database-Level Constraints
Database-level constraints specify some sort of functional determinant between the key and its dependent attributes (see Functional, Transitive, and Multivalued Compatibilities) as well as the functional determinants among two or more tables (see Inclusion Compatibilities).
The most commonly observed database-level constraint between tables is the primary key-foreign key relationship whose enforcement is referred to as maintaining referential integrity. This constraint specifies that you cannot delete a row having primary key value x from table X as long as any foreign key value in table Y has value x on the column set that defines the referential integrity relationship between those columns in tables X and Y. In other words, if you have foreign key value x, then you must also have primary key value x if referential integrity is defined for those keys and tables.
The specific table-level constraint syntax used to define the common PK-FK referential integrity constraint is FOREIGN KEY (referencing_column_set) REFERENCES referenced_table_name (referenced_primary_key_column_name_set).
Though less frequently used, it is also possible to specify and enforce database-level constraints on non-PK-non-FK column relationships if the columns defining those relationships are alternate keys.
The specific constraint used to define an alternate key referential integrity constraint is a foreign key constraint with column-level syntax REFERENCES … table_name … alt_key_name and table-level syntax FOREIGN KEY (referencing_column_set) REFERENCES referenced_table_name (referenced_alt_key_name), where alt_key_name refers to an alternate key column set in the parent table.
The database-level constraints for your databases are developed from the ATM Constraints form. For more information, see Constraints Form.
You cannot declare database-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 Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.