15.00 - Referential Integrity Constraints - Teradata Database

Teradata Database Design

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

Referential Integrity Constraints

The combination of the foreign key, the parent key (more accurately, a candidate key), and the relationship between them defined by the referential integrity rule is called the referential integrity constraint.

This is a constraint defined on a table column set (using the CREATE TABLE or ALTER TABLE SQL statements) that represents a referential integrity link between two tables.

A referential integrity constraint is always defined for a foreign key column set in the child table in a relationship.

Note that you cannot use UDT, Period, Geospatial, BLOB, CLOB, or XML columns to define a referential integrity relationship or other database constraint (see Chapter 12: “Designing for Database Integrity”).

See Chapter 12: “Designing for Database Integrity” for additional information about how referential integrity is essential to maintaining the integrity of databases.

Teradata Database provides two other features related to referential integrity constraints: batch referential integrity constraints and referential constraints. The basic differences among the different referential constraint types are summarized in the following table.

 

Referential Constraint Type

 

CREATE TABLE Syntax

Does It Enforce Referential Integrity?

Level of Referential Integrity Enforcement

  • Referential Constraint
  • Temporal Relationship Constraint
  • For more information, see ANSI Temporal Table Support and Temporal Table Support.

    REFERENCES WITH NO CHECK OPTION

    No

    None

    Batch referential integrity constraint

    REFERENCES WITH CHECK OPTION

    Yes

    All child table rows must match a parent table row

    Referential integrity constraint

    REFERENCES

    Yes

    Row

    Referential constraints and temporal relationship constraints do not enforce the referential integrity of the database. Instead, they signal the Optimizer that certain referential relationships are in effect between tables, thus providing a means for producing better query plans without incurring the overhead of system enforcement of the specified RI constraints.

    You should specify referential constraints and temporal relationship constraints only when you enforce the integrity of the referential relationship in some other way, or if the possibility of query errors and the potential for data corruption is not critical to your application. See “Foreign Key Constraints” on page 644 and “CREATE TABLE Column Definition Clause” in SQL Data Definition Language for more information.

    Batch referential integrity constraints are externally identical to regular referential integrity constraints. Because they enforce referential integrity in an all‑or‑none manner for an entire implicit transaction, they can be more high-performing than standard referential integrity constraints. In most circumstances, there is no semantic difference between the two, only an implementation difference. Batch referential integrity becomes important when a single statement inserts many rows into a table, like the massive inserts that can occur with INSERT … SELECT requests.

    Example : Referential Integrity Constraints

    The following CREATE TABLE statement defines the following referential integrity constraints on table_A.

  • A column-level constraint on the foreign key column, column_A1, and the parent table (table_B) key column, column_B1.
  • A table-level constraint on the composite foreign key column set (column_A1, column_A2) and the parent table table_C.
  •     CREATE TABLE table_A (
         column_A1 CHARACTER(10) REFERENCES table_B (column_B1), 
         column_A2 INTEGER
        PRIMARY KEY (column_A1),
        FOREIGN KEY (column_A1, column_A2) REFERENCES table_C);

    According to this definition, the single-column primary key column_A1 must reference the primary key column column_B1 of the parent table table_B. Table_B must have a primary key, of which one column is column_B1, having the following definition.

          CHARACTER(10) NOT NULL

    The composite foreign key (column_A1, column_A2) must reference the primary key of the parent table table_C. Table_C must have a two-column primary key, the first column of which has the following definition.

          CHARACTER(10) NOT NULL

    The second column of the Table_C primary key must have the following definition.

          INTEGER NOT NULL