REFERENCES - Advanced SQL Engine - Teradata Database

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.