Foreign Key Constraints - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Foreign key constraints permit you to specify referential primary key-foreign key relationships between a unique column set in the current base table and an alternate key column set in a different base table. The FOREIGN KEY keywords are required for table-level foreign key definitions but cannot be used for column-level foreign key definitions;. If you specify FOREIGN KEY, then you must also specify a REFERENCES clause. Vantage uses referential integrity constraints to enforce referential integrity (see Inclusion Compatibilities) and to optimize SQL requests (see Eliminating Redundant Joins ). The overhead of enforcing standard referential integrity constraints is summarized in Referential Integrity Constraint Checks and Overhead Costs of Standard Referential Integrity.

You can also use a foreign key definition to specify any of the following referential constraint types.
  • Standard Referential Integrity
  • Batch Referential Integrity
  • Referential Constraints
  • Temporal Relationship Constraints

Temporal tables can only be defined for so-called “soft” referential integrity relationships, or Referential Constraints, and for Temporal Relationship Constraints, and do not permit foreign key constraints for Standard or Batch Referential Integrity.

When you specify the REFERENCES WITH NO CHECK OPTION phrase, Vantage does not enforce the defined referential integrity constraint. This implies the following things about child table rows:
  • A row having a value, meaning that the implication is false if the referencing column is null, for a referencing column can exist in a table even when no equivalent parent table value exists.
  • A row can match multiple rows in its parent table when the referenced and referencing column values are compared.

    This can happen because the candidate key acting as the primary key for the referenced table in the constraint need not be explicitly declared to be unique. See the list of rules for Referential Constraints later in this topic for details.

The types have different applications as described in the following table.

Referential Constraint Type Application
Referential Integrity Constraints
  • Tests each individual inserted, deleted, or updated row for referential integrity.
  • If insertion, deletion, or update of a row violates referential integrity, AMP software rejects the operation and returns an error message.
  • Permits special optimization of certain queries.
Batch Referential Integrity Constraints
  • Tests an entire insert, delete, or update batch operation for referential integrity.
  • If insertion, deletion, or update of any row in the batch violates referential integrity, parsing engine software rolls back the entire batch and returns an abort message.
  • Permits special optimization of certain queries.
Referential Constraints
  • Does not test for referential integrity.
  • Assumes that the user somehow enforces referential integrity in a way other than the normal declarative referential integrity constraint mechanism.
  • Permits special optimization of certain queries.
Temporal Relationship Constraint (TRC)
  • Does not test for referential integrity.
  • Assumes that the user somehow enforces referential integrity in a way other than the normal declarative referential integrity constraint mechanism.
  • Permits special optimization of certain queries.
  • TRC relationships can only be defined at the table level.
  • TRC relationships cannot be defined on self-referencing tables.

Referential Constraints do not enforce primary key-foreign key constraints between tables, thereby avoiding the overhead of RI enforcement by the system as practiced by standard and batch referential integrity constraints. Their only purpose is to provide the Optimizer with a means for devising better query plans. Referential Constraints must be used only for situations for which referential integrity is either not important or is enforced by other means, because its use implicitly instructs the system to trust the validity of all DML requests made against the affected columns and not to check the specified referential integrity relationships.

Important: If referential integrity errors occur, data corruption can occur. Erroneous results can be returned if a DML request specifies a redundant RI join and the primary key-foreign key rows do not match.

For more information, see Inclusion Compatibilities and the information about CREATE TABLE in CREATE TABLE and CREATE TABLE AS .

The following table summarizes the differences among the different referential constraints.

Referential Constraint Type CREATE/ALTER TABLE Definition Clause Description Enforces Referential Integrity Level of Referential Integrity Enforcement Join Elimination Optimizations Derived Statistics Propagated Between Child and Parent Tables Pros Cons
Standard Referential Integrity Constraint REFERENCES ANSI/ISO SQL:2011 compliant.

Integrity enforcement done using a Referential Index (see Sizing a Reference Index Subtable).

 Yes Row Yes Yes
  • ANSI/ISO compliant.
  • Logs RI violations in an error table.
  • Efficient for low volume updates.
 Enforced one row at a time, so not efficient for medium to large updates.
Batch Referential Integrity Constraint REFERENCES WITH CHECK OPTION Teradata extension to ANSI/ISO SQL:2011 standard.

All or nothing for update operations.

Enforced using the following methods:

  • Joining new child table keys to parent table to make sure the new keys exist.
  • Joining deleted parent table keys to child table to make sure the deleted keys do not exist.
 Yes Implicit transaction Yes Yes Efficient for medium to large updates because uses the Optimizer to determine best way to make the join.
  • Can be slower than regular RI for small updates.
  • Does not log RI violations in an error table.
Referential Constraint

(“soft referential constraint”)

 REFERENCES WITH NO CHECK OPTION Teradata extension to ANSI/ISO SQL:2011 standard.

No RI enforcement by Vantage. You must check the integrity of the relationship yourself.

Not necessary to define a UNIQUE constraint for the primary or alternate key column set.

 No None Yes Yes No cost for enforcing RI or uniqueness.
  • Incorrect results or data corruption can occur if the RI constraint is not valid.
  • Not an issue if you are certain about the integrity of the relationship.
Temporal Relationship Constraint (“TRC constraint”)  
When you specify the REFERENCES WITH NO CHECK OPTION phrase, Vantage does not enforce the defined referential integrity constraint. This implies the following things about child table rows.
  • A row having a non-null value for a referencing column can exist in a table even when no equivalent parent table value exists.
  • A row can match multiple rows in its parent table when the referenced and referencing column values are compared.

    This can happen because the candidate key acting as the primary key for the referenced table in the constraint need not be explicitly declared to be unique. See the list of rules for Referential Constraints later in this topic for details.

Referential Constraints do not enforce primary key-foreign key constraints between tables, thereby avoiding the overhead of RI enforcement by the system as practiced by standard and batch referential integrity constraints. Their only purpose is to provide the Optimizer with a means for devising better query plans. Referential Constraints must be used only for situations for which referential integrity is either not important or is enforced by other means, because its use implicitly instructs the system to trust the validity of all DML requests made against the affected columns and not to check the specified referential integrity relationships.

If referential integrity errors occur, data corruption can occur. Erroneous results can be returned if a DML request specifies a redundant RI join and the primary key-foreign key rows do not match.

For more information see Inclusion Compatibilities and the information about CREATE TABLE in CREATE TABLE and CREATE TABLE AS .

Rules for Both Column-Level and Table-Level Foreign Key Constraints

The following rules apply to both column- and table-level FOREIGN KEY ... REFERENCES constraints:
  • Teradata does not support the following ANSI/ISO SQL:2011 referential action options for FOREIGN KEY ... REFERENCES constraints:
    • MATCH {FULL | PARTIAL | SIMPLE}
    • ON UPDATE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}
    • ON DELETE {CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}
  • The specified column_name list must be identical to a set of columns in the referenced table that is defined as one of the following.
    • PRIMARY KEY
    • UNIQUE
    • A unique secondary index

      This rule is optional for Referential Constraints. See Referential Constraints for details.

      The specified table_name refers to the referenced table, which must be a user base data table, not a view.

  • A maximum of 64 foreign keys can be defined for a table and a maximum of 64 referential constraints can be defined for a table.

    Similarly, a maximum of 64 other tables can reference a single table. Therefore, there is a maximum of 128 Reference Indexes that can be stored in the table header per table.

    The limit on Reference Indexes includes both references to and from the table and is derived from 64 references to other tables plus 64 references from other tables = 128 Reference Indexes.

    However, only 64 Reference Indexes are stored per Reference Index subtable for a table, those that define the relationship between the table as a parent and its children.

    Column-level CHECK constraints that reference alternate keys in other tables do not count against this limit.

  • Each individual foreign key can be defined on a maximum of 64 columns.
  • Foreign key constraint attributes can be null and are rarely unique.

    An example of when a foreign key is unique is when a logical table is vertically partitioned into multiple tables.

  • Each column in the foreign key constraint must correspond with a column of the referenced table, and the same column name must not be specified more than once.
  • The referencing column list must contain the same number of column names as the referenced column list. The ith column of the referencing list corresponds to the ith column identified in the referenced list. The data type of each referencing column must be the same as the data type of the corresponding referenced column.
  • The user issuing the CREATE TABLE request that specifies a foreign key constraint must either have the REFERENCE privilege on the referenced table or on all specified columns of the referenced table.
  • If REFERENCES is specified in a column_constraint,then table_name defines the referenced table. The table_name must be a base table, not a view.
  • Referential constraints are not supported for global temporary, global temporary trace, or volatile tables.
  • You can create a child table when its parent table does not yet exist, but a REFERENCES constraint that makes a forward reference to a table that has not yet been created cannot qualify the parent table name with a database name.

    That is, the forward-referenced parent table that has not yet been created must be assumed to be “contained” in the same database as its child table that is being created.

  • You cannot define a foreign key constraint on a row-level security-protected column.

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.
  • A specified column_name 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 must be unique even if it is not explicitly declared to be so. This is true by definition. See PRIMARY KEY Constraints for details.

    The uniqueness rule for candidate keys is necessary because the Optimizer 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. Therefore, you must make sure that all candidate key values are unique even if they are not explicitly declared to be so.

    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 Vantage does not enforce either constraint.

Rules for Table-Level FOREIGN KEY ... REFERENCES Constraints Only

  • You must specify a complete FOREIGN KEY (referencing_column_set) REFERENCES (referenced_table_name) specification for the foreign key definition.
  • If you do not specify the optional referenced_column_set in the foreign key definition, Vantage assumes that the columns in the referencing column set have the identical names as the implied columns in the referenced column set.
  • If the referenced column set columns do not have the same names as their counterparts in the referencing_column_set list, you must specify their names using the FOREIGN KEY (referencing_column_set) REFERENCES referenced_table_name (referenced_column_set) syntax.
  • The optional keywords WITH CHECK OPTION and WITH NO CHECK OPTION define a foreign key constraint as being either a batch referential integrity constraint or a Referential Constraint, respectively.

    If you specify neither set of keywords, the foreign key constraint defines a traditional foreign key constraint by default.

  • A maximum of 100 table-level constraints can be defined for any table.

Rules for FOREIGN KEY ... REFERENCES Referential Constraints Only

Other than their not enforcing referential integrity, most rules for Referential Constraints are identical to those documented by Rules for Both Column-Level and Table-Level Foreign Key Constraints and by Rules for Table-Level FOREIGN KEY ... REFERENCES Constraints Only earlier in this topic.

The exceptions are documented by the following set of rules that apply specifically to the specification and use of Referential Constraints.
  • You can specify standard RI, batch RI, and Referential Constraints in the same table, but not for the same column set.
  • You can specify Referential Constraints for both of the following constraint types:
    • FOREIGN KEY (FK_column_set) REFERENCES (parent_table_PK_column_set)
    • (NFK_column_set) REFERENCES (parent_table_AK_column_set)

      where NFK indicates non-foreign key and parent_table_AK_column_set indicates an alternate key in the parent table.

  • Though not enforced by the system, Referential Constraint references count toward the maximum of 64 references permitted for a table referenced as a parent.
  • INSERT, DELETE, and UPDATE requests are not permitted against tables with unresolved, inconsistent, or non-valid Referential Constraints. This rule is identical to the rule enforced for standard and batch RI.
  • The candidate key acting as the primary key in the referenced table in the constraint need not be explicitly declared to be unique using the PRIMARY KEY or UNIQUE keywords, or be declared to be a USI in the table definition.

Referential Integrity Constraint Checks

Vantage performs referential integrity constraint checks whenever any of the following things occur:
  • A referential integrity constraint is added to a populated table.
  • A row is inserted or deleted.
  • A parent or foreign key is modified.

The following table summarizes these actions.

Action Taken Constraint Check Performed
INSERT into parent table None.
INSERT into child table Must have matching parent key value if the foreign key is not null.
DELETE from parent table Abort the request if the deleted parent key is referenced by any foreign key.
DELETE from child table None.
UPDATE parent table Abort the request if the parent key is referenced by any foreign key.
UPDATE child table New value must match the parent key when the foreign key is updated.

Overhead Costs of Standard Referential Integrity

By implementing standard referential integrity, you incur certain overhead costs that can have a negative effect on performance. The following table lists the referential integrity overhead operations that affect performance.

Operation Description
Insert, update, or delete rows in a referencing table. Overhead is similar to that for USI subtable row handling.

A redistributed spool for each reference is dispatched to the AMP containing the subtable entry.

BYNET traffic incurs the majority of the cost of this operation.
Insert a row into a referencing table A referential integrity check is made against the Reference Index subtable.

If the referenced column is in the Reference Index subtable, the count in the Reference Index subtable is incremented.

If the referenced column is not in the Reference Index subtable, Vantage first checks the Reference Index subtable to verify that the referenced column exists. If so, an entry with a count of 1 is added to the Reference Index subtable.
Delete a row from a referencing table A referential integrity check is made against the Reference Index subtable and its count for the referenced field is decremented.

When the count decrements to 0, then the subtable entry for the Referenced field is deleted.
Update a referenced field in a referencing table Overhead is similar to that for changing the value of a USI column.

A referential integrity check is made against the Reference Index subtable. Both the inserting-a-row and deleting-a-row operations run on the Reference Index subtable, decrementing the count of the old Referenced column value and incrementing the count of the new Reference column value.
Delete a row from a referenced table The Reference Index subtable is checked to verify that the corresponding Referenced column does not exist. When nonexistence is confirmed, the row is deleted from the Referenced table.

No BYNET traffic is involved because the Referenced column is the same value in the Referenced table and the Reference Index subtable.