15.00 - Database-Level Constraints - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Database-Level Constraints

Database‑level constraints specify some sort of functional determinant between the key and its dependent attributes (see “Functional, Transitive, and Multivalued Dependencies” on page 80) as well as the functional determinants among two or more tables (see “Inclusion Dependencies” on page 632).

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 (see “Constraints Form” on page 142).

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 SQL Data Definition Language.