REFERENCES - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
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
Referential integrity constraint. The integrity of the relationship is checked only when the entire containing transaction 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 Summary of Fundamental Database Principles for information about the Referential Integrity rule.

This option is not valid for temporal tables.

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.
Referential integrity is supported only on the Block File System on the primary cluster, not on the Object File System.

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.

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