Rules for Both Column-Level and Table-Level Foreign Key Constraints - Teradata Database

Rules for Both Column‑Level and Table‑Level Foreign Key Constraints

The following rules apply to both column- and table-level FOREIGN KEY … REFERENCES constraints:

Teradata Database does not support the following ANSI/ISO SQL:2011 referential action options for FOREIGN KEY … REFERENCES constraints:

MATCH {FULL | PARTIAL | SIMPLE}

ON UPDATE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}

ON DELETE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}

The specified column_name list must be identical to a set of columns in the referenced table that is defined as one of the following.

PRIMARY KEY

UNIQUE

A unique secondary index

This rule is not mandatory for Referential Constraints. See “Foreign Key Constraints” on page 594 for details.

The specified table_name refers to the referenced table, which must be a user base data table, not a view.

A maximum of 64 foreign keys can be defined for a table and a maximum of 64 referential constraints can be defined for a table.

Similarly, a maximum of 64 other tables can reference a single table. Therefore, there is a maximum of 128 Reference Indexes that can be stored in the table header per table.

The limit on Reference Indexes includes both references to and from the table and is derived from 64 references to other tables plus 64 references from other tables = 128 Reference Indexes.

However, only 64 Reference Indexes are stored per Reference Index subtable for a table, those that define the relationship between the table as a parent and its children.

Column-level CHECK constraints that reference alternate keys in other tables do not count against this limit.

Each individual foreign key can be defined on a maximum of 64 columns.

Foreign key constraints cannot be defined on columns defined with any of the following data types:

XML

BLOB

CLOB

XML‑based UDT

BLOB‑based UDT

CLOB‑based UDT

UDT

ARRAY/VARRAY

Period

Geospatial

JSON

Foreign key constraints cannot be defined on a global temporary trace table.

Note the following attributes of foreign key constraints:

They can be null.

They are rarely unique.

An example of when a foreign key would be unique is the case of a vertical partitioning of a logical table into multiple tables.

Each column in the foreign key constraint must correspond with a column of the referenced table, and the same column name must not be specified more than once.

The referencing column list should contain the same number of column names as the referenced column list. The i^th column of the referencing list corresponds to the i^th column identified in the referenced list. The data type of each referencing column must be the same as the data type of the corresponding referenced column.

The user issuing the CREATE TABLE request that specifies a foreign key constraint must either have the REFERENCE privilege on the referenced table or on all specified columns of the referenced table.

If REFERENCES is specified in a column_constraint, then table_name defines the referenced table. Note that table_name must be a base table, not a view.

Referential constraints are not supported for global temporary, global temporary trace, or volatile tables.

While it is possible to create a child table at a time that its parent table does not yet exist, a REFERENCES constraint that makes a forward reference to a table that has not yet been created cannot qualify the parent table name with a database name.

In other words, the forward‑referenced parent table that has not yet been created must be assumed to be “contained” in the same database as its child table that is currently being created.

You cannot define a foreign key constraint on a row‑level security‑protected column.