Rules for Column-Level Foreign Key Constraints Only - Teradata Database

Rules for Column‑Level Foreign Key Constraints Only

The following rules apply to column-level foreign key constraints only.

You cannot specify the keywords FOREIGN KEY or specify a referencing column set in the definition of a column‑level foreign key constraint.

The referencing column for a column‑level foreign key constraint is the column on which the foreign key constraint is defined by default.

You cannot define a multicolumn foreign key constraint using the column‑level foreign key syntax.

If you omit column_name, the referenced table must have a single‑column primary key, and the specified foreign key column references that primary key column of the referenced table.

If you specify column_name, it must refer to the single‑column primary key of the referenced table or to a single‑column alternate key in the referenced table defined as UNIQUE.

This rule does not apply for Referential Constraints. In such cases, the candidate key acting as the primary key in the referenced table need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords or by declaring it to be a USI in the table definition.

However, the candidate key in the relationship actually must always be unique even if it is not explicitly declared to be so. This is true by definition. See “Identifying Candidate Primary Keys” on page 92 and “PRIMARY KEY Constraints” on page 604 for details.

The uniqueness rule for candidate keys is necessary because the Optimizer always assumes that candidate keys are unique when it generates its query plans, and if that assumption is not true, it is not only possible to produce incorrect results to queries, but to corrupt databases. Because of this, you must always ensure that all candidate key values are unique even if they are not explicitly declared to be so.

As is always true when you specify Referential Constraints, you must assume responsibility for ensuring that any candidate key in a referential integrity relationship is unique, just as you must assume responsibility for ensuring that the referential relationship it anchors holds true in all cases, because Teradata Database enforces neither constraint.