17.05 - ADD FOREIGN KEY REFERENCES - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

Add a foreign key reference to a key in another table.


You must specify FOREIGN KEY referencing_column_name before you specify REFERENCES referenced_table_name (referenced_column_name).


For an unnamed FOREIGN KEY REFERENCES table constraint, use this syntax:
     ADD FOREIGN KEY (referencing_column) REFERENCES 
              referenced_table_name(referenced_column_name)

For a named FOREIGN KEY REFERENCES table constraint, use this syntax:

     ADD CONSTRAINT  constraint_name  FOREIGN KEY 
             (referencing_column) REFERENCES 
              referenced_table_name  (referenced_column_name)
(column_name_list)
One or more columns that reference a primary key or alternate key in referenced_table_name.
table_name

Referenced table name is the name of the table that contains the primary key or alternate key referenced by the referencing_column set.
You must either have the REFERENCES privilege on the referenced table or on all specified columns of the referenced table.
A table can have a maximum of 64 foreign keys and a maximum of 64 referential constraints.
A maximum of 64 other tables can reference a single table. A maximum of 128 reference indexes can be stored in the table header per table. However, 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.
column_name
Referenced column name is the column set in referenced_table_name that is referenced by the referencing_column set.
CONSTRAINT
Keyword for a named constraint.
constraint_name
Name of the table attribute foreign key constraint. The constraint_name is optional. You can specify a foreign key REFERENCES constraint as a table attribute or a column attribute. Vantage uses a different syntax for the two forms of foreign key. Foreign key REFERENCES constraints can be:
  • null
  • unique, but this is rare. An example of a unique foreign key is a logical table that is vertically partitioned into multiple tables.
You cannot add foreign key constraints on columns having any of the following data types:
  • BLOB
  • CLOB
  • ARRAY/VARRAY
  • Period
  • UDT
  • XML
  • Geospatial
  • JSON
  • DATASET
You cannot specify a foreign key constraint on a row-level security constraint column.
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 information on the various types of constraints, see CREATE TABLE in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
Although you can create a child table before the parent table has been created, 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.
The forward-referenced parent table that has not yet been created must be in the same database as child table being created.
Each column in the foreign key referenced_column_name list must correspond to a column of referenced_table_name in REFERENCES 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 match the data type of the corresponding REFERENCES column.
Each foreign key can be defined on a maximum of 64 columns.
You can be define a maximum of 100 table-level constraints for a table.
You cannot specify a foreign key REFERENCES constraint on:
  • an identity column
  • global temporary trace table
  • global temporary table
  • volatile table
  • queue table
See CREATE TABLE (Queue Table Form).
Foreign key REFERENCES constraints cannot be copied to a new table using the CREATE TABLE AS syntax.
WITH CHECK OPTION
A referential integrity constraint. The integrity of the relationship is checked only when the entire transaction of which it is a component completes.
For a table attribute foreign key constraint, specify this clause:
FOREIGN KEY (referencing_column_name) REFERENCES 
             WITH CHECK OPTION  referenced_table_name  
                         (referenced_column_name)
If any of the rows in the transaction violate the Referential Integrity rule, then the entire transaction is rolled back.
See Teradata Vantage™ - Database Design, B035-1094 for information about the Referential Integrity rule.
This option is not valid for temporal tables. For details, see Teradata Vantage™ - Temporal Table Support , B035-1182 .
This clause is a Teradata extension to the ANSI SQL:2011 standard.