Maintaining Foreign Keys - Advanced SQL Engine - Teradata Database

SQL Fundamentals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-28
dita:mapPath
uhe1592872955107.ditamap
dita:ditavalPath
uhe1592872955107.ditaval
dita:id
B035-1141
lifecycle
previous
Product Category
Teradata Vantage™

Definition of a FOREIGN KEY requires that Vantage maintain the integrity defined between the referenced and referencing table.

Vantage maintains the integrity of foreign keys as explained in the following table.

For this data manipulation activity … The system verifies that …
A row is inserted into a referencing table and foreign key columns are defined to be NOT NULL. a row exists in the referenced table with the same values as those in the foreign key columns.

If such a row does not exist, then an error is returned.

If the foreign key contains multiple columns, and if any one column value of the foreign key is null, then none of the foreign key values are validated.

The values in foreign key columns are altered to be NOT NULL. a row exists in the referenced table that contains values equal to the altered values of all of the foreign key columns.

If such a row does not exist, then an error is returned.

A row is deleted from a referenced table. no rows exist in referencing tables with foreign key values equal to those of the row to be deleted.

If such rows exist, then an error is returned.

Before a referenced column in a referenced table is updated. no rows exist in a referencing table with foreign key values equal to those of the referenced columns.

If such rows exist, then an error is returned.

Before the structure of columns defined as foreign keys or referenced by foreign keys is altered. the change would not violate the rules for definition of a foreign key constraint.

An ALTER TABLE or DROP INDEX statement attempting to change such a columns structure returns an error.

A table referenced by another is dropped. the referencing table has dropped its foreign key reference to the referenced table.
An ALTER TABLE statement adds a foreign key reference to a table.

The same processes occur whether the reference is defined for standard or for soft referential integrity.

all of the values in the foreign key columns are validated against columns in the referenced table.
When the system parses ALTER TABLE, it defines an error table that:
  • Has the same columns and primary index as the target table of the ALTER TABLE statement.
  • Has a name that is the same as the target table name suffixed with the reference index number.

    A reference index number is assigned to each foreign key constraint for a table.

    To determine the number, use one of the following system views: RI_Child_TablesV, RI_Distinct_ChildrenV, RI_Distinct_ParentsV, RI_Parent_TablesV.

  • Is created under the same user or database as the table being altered.

    If a table already exists with the same name as that generated for the error table then an error is returned to the ALTER TABLE statement.

Rows in the referencing table that contain values in the foreign key columns that cannot be found in any row of the referenced table are copied into the error table (the base data of the target table is not modified).

It is your responsibility to:
  • Correct data values in the referenced or referencing tables so that full referential integrity exists between the two tables.

    Use the rows in the error table to define which corrections to make.

  • Maintain the error table.