REFERENCES - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™
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.