FOREIGN KEY - Advanced SQL Engine - Teradata Database

A foreign key reference from a column set in this table to the primary key or an alternate key in another table.

You can specify a foreign key REFERENCES constraint as a column attribute or as a table attribute.

See Teradata Vantage™ - SQL Fundamentals, B035-1141 for the rules for naming database objects.

The following rules apply to foreign key REFERENCES constraints.

• You must either have the REFERENCES privilege on the referenced table or on all specified columns of the referenced table.
• 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, but only 64 of these, the reference indexes that map the relationship between the table and its child tables, are stored per reference index subtable. The table header limit on reference indexes includes both references to and from the table.

  For information about temporal tables and foreign key REFERENCES constraints, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .

Although you can create a child table before the parent table exists, a foreign key 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.

The forward-referenced parent table that has not yet been created is assumed to be in the same database as the child table currently being created.

Each column in the foreign key referenced_column_name list must correspond to a column of referenced_table_name in REFERENCES referenced_table_name, and you cannot specify the same column name more than once.

The foreign key column list must contain the same number of column names as the referenced primary or alternate key in table_name. The i th column of the referencing list corresponds to the i th column identified in the referenced list.

The data type of each foreign key referencing column must be the same as the data type of the corresponding REFERENCES referenced column.

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

A maximum of 100 table-level constraints can be defined for any table.

You cannot specify foreign key REFERENCES constraints on columns with the following data types:

• BLOB
• CLOB
• UDT
• ARRAY/VARRAY
• Period
• XML
• Geospatial
• JSON
• DATASET

You cannot specify a foreign key REFERENCES constraint on an identity column.

Foreign key REFERENCES constraints can be null.

Though foreign key REFERENCES constraints can be unique, this is rare.

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

You cannot specify foreign key REFERENCES constraints for global temporary trace, volatile, or queue tables. See CREATE TABLE (Queue Table Form).

Foreign key REFERENCES constraints cannot be copied to a new table using the copy table syntax.

You can specify a mix of standard referential integrity constraints, batch referential integrity constraints, and Referential Constraints for the same table, but not for the same column sets. For a description, see “CREATE TABLE” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

Example: Specifying Batch Referential Integrity Constraints

This example creates a table-level batch referential integrity constraint on column d1 of child table drs.t2, which refers to column c1 in parent table drs.t1. Referential integrity is enforced for this constraint.

    CREATE SET TABLE drs.t1, NO FALLBACK,
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL
         (
          c1 INTEGER NOT NULL,
          c2 INTEGER NOT NULL,
          c3 INTEGER NOT NULL)
    UNIQUE PRIMARY INDEX (c1); 
    CREATE SET TABLE drs.t2, NO FALLBACK,
         NO BEFORE JOURNAL,
         NO AFTER JOURNAL
         (
          d1 INTEGER,
          d2 INTEGER,
          d3 INTEGER,
    FOREIGN KEY (d1) REFERENCES WITH CHECK OPTION drs.t1 (c1));

Example: Specifying a Table-Level Foreign Key Constraint

The first request creates a table-level standard referential integrity constraint on foreign key column a2 with column d1 in table d. Referential integrity is enforced for this relationship.

The second request creates a table-level Referential Constraint relationship on column a2 with column d1 in table d. Referential integrity is not enforced for this relationship.

    CREATE TABLE a (
     a1 INTEGER,
     a2 CHARACTER(10),
     a3 INTEGER,
    FOREIGN KEY (a2) REFERENCES d(d1))
    PRIMARY INDEX (a1); 
    
    CREATE TABLE a (
     a1 INTEGER,
     a2 CHARACTER(10),
     a3 INTEGER,
    FOREIGN KEY (a2) REFERENCES WITH NO CHECK OPTION d(d1))
    PRIMARY INDEX (a1);