15.00 - Rules for Referential Integrity Constraints - Teradata Database

Teradata Database Design

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

Rules for Referential Integrity Constraints

Referential integrity constraints must obey the following set of rules:

  • The parent key must exist when the referential integrity constraint is defined.
  • The parent key columns must be either a unique primary index (UPI) or a unique secondary index (USI).
  • The foreign and parent keys must have the same number of columns and their data types must match.
  • The foreign and parent keys cannot exceed 64 columns.
  • Duplicate referential integrity constraints are not allowed.
  • The columns on which a referential integrity relationship is defined cannot be defined with the XML, BLOB, CLOB, ARRAY, VARRAY, or Geospatial data types.
  • You cannot drop or alter foreign key or parent key columns using an ALTER TABLE statement after a referential integrity constraint has been defined on them.
  • To drop a foreign or parent key column after a referential integrity constraint has been defined on it, you must first drop the referential constraint and then alter the table to drop the foreign or parent key columns.

  • A foreign key value must be equal to its parent key value or it must be null.
  • Self-reference (a condition in which the Parent and Child tables are the same table) is allowed, but the foreign and parent keys cannot consist of identical columns.
  • You can define no more than 64 referential integrity constraints per table.
  • You cannot define a referential integrity relationship on a global temporary trace table. See “CREATE GLOBAL TEMPORARY TRACE TABLE” in SQL Data Definition Language Detailed Topics.