15.10 - REFERENCES - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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 Database Design, B035-1094 for information about the Referential Integrity rule.

This option is not valid for temporal tables. For details, see 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 Temporal Table Support, B035-1182.

See 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.