17.10 - REFERENCES - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

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

A reference as a simple column attribute.
The following rules apply to column attribute REFERENCES constraints only.

  • FOREIGN KEY is only used for table constraint foreign key specifications.
  • If you do not specify column_name, the referenced table must have a simple primary key, and the specified foreign key column references that primary key column in the referenced table.

You cannot specify a REFERENCES constraint on a column with the JSON data type.

For an unnamed REFERENCES column constraint, use this syntax:

    REFERENCES referenced_table_name 
                        (referenced_column_name)
referenced_table_name
The name of the table that contains the primary key or alternate key referenced by the specified referencing_column.
referenced_column_name
The name of the table that contains the primary key or alternate key referenced by referencing_column.
CONSTRAINT constraint_name
The name of the column attribute foreign key constraint.

For a named REFERENCES column constraint, use this syntax:

    CONSTRAINT constraint_name REFERENCES referenced_table_name
                         (referenced_column_name)
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 column attribute foreign key constraint, specify this clause:

REFERENCES WITH CHECK OPTION referenced_table_name 
                            (referenced_column_name)

If any of the rows in the transaction violate the Referential Integrity rule, 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.

WITH NO CHECK OPTION
The constraint to be added or dropped is not to be used to validate referential integrity for the specified primary key-foreign key relationship.

For a column attribute foreign key constraint, specify this clause as follows.

REFERENCES WITH NO CHECK OPTION referenced_table_name 
                               (referenced_column_name)

This is the only valid referential integrity option for temporal tables. For details, see Teradata Vantage™ - Temporal Table Support, B035-1182.

See Teradata Vantage™ - Temporal Table Support, B035-1182 for information about how to use this clause with temporal tables.

This clause is a Teradata extension to the ANSI SQL:2011 standard.