15.00 - Referential Integrity - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

Referential Integrity

Referential integrity (RI) is defined as all the following notions.

  • The concept of relationships between tables, based on the definition of a primary key (or UNIQUE alternate key) and a foreign key.
  • A mechanism that provides for specification of columns within a referencing table that are foreign keys for columns in some other referenced table.
  • Referenced columns must be defined as one of the following.

  • Primary key columns
  • Unique columns
  • A reliable mechanism for preventing accidental database corruption when performing inserts, updates, and deletes.
  • Referential integrity requires that a row having a non-null value for a referencing column cannot exist in a table if an equal value does not exist in a referenced column.

    Referential Integrity Enforcement

    Teradata Database supports two forms of declarative SQL for enforcing referential integrity:

  • A standard method that enforces RI on a row-by-row basis
  • A batch method that enforces RI on a statement basis
  • Both methods offer the same measure of integrity enforcement, but perform it in different ways.

    A third form, sometimes informally referred to as soft RI, is related to these because it provides a declarative definition for a referential relationship, but it does not enforce that relationship. Enforcement of the declared referential relationship is left to the user by any appropriate method.

    Referencing (Child) Table

    The referencing table is referred to as the child table, and the specified child table columns are the referencing columns.

    Note: Referencing columns must have the same numbers and types of columns, data types, and sensitivity as the referenced table keys. Column-level constraints are not compared and, for standard referential integrity, compression is not allowed on either referenced or referencing columns.

    Referenced (Parent) Table

    A child table must have a parent, and the referenced table is referred to as the parent table.

    The parent key columns in the parent table are the referenced columns.

    For standard and batch RI, the referenced columns must be one of the following unique indexes:

  • A unique primary index (UPI), defined as NOT NULL
  • A unique secondary index (USI), defined as NOT NULL
  • Soft RI does not require any index on the referenced columns.

    Terminology

     

    Term

    Definition

    Child Table

    A table where the referential constraints are defined.

    Child table and referencing table are synonyms.

    Parent Table

    The table referenced by a child table.

    Parent table and referenced table are synonyms.

    Primary Key

    A unique identifier for a row of a table.

     

    UNIQUE Alternate Key

    Foreign Key

    A column set in the child table that is also the primary key (or a UNIQUE alternate key) in the parent table.

    Foreign keys can consist of as many as 64 different columns.

    Referential Constraint

    A constraint defined on a column set or a table to ensure referential integrity.

    For example, consider the following table definition:

    CREATE TABLE A
    (A1 CHAR(10) REFERENCES B (B1), /* 1 */
     A2 INTEGER
    FOREIGN KEY (A1,A2) REFERENCES C /* 2 */
    PRIMARY INDEX (A1));

    This CREATE TABLE statement specifies the following referential integrity constraints.

  • Constraint 1 is defined at the column level. Implicit foreign key A1 references the parent key B1 in table B.
  • Constraint 2 is defined at the table level.
  • Explicit composite foreign key (A1, A2) implicitly references the UPI (or a USI) of parent table C, which must be two columns, the first typed CHAR(10) and the second typed INTEGER.

    Both parent table columns must also be defined as NOT NULL.

     

    Why Referential Integrity Is Important

    Consider the employee and payroll tables for any business.

    With referential integrity constraints, the two tables work together as one. When one table gets updated, the other table also gets updated.

    The following case depicts a useful referential integrity scenario.

    Looking for a better career, Mr. Clark Johnson leaves his company. Clark Johnson is deleted from the employee table.

    The payroll table, however, does not get updated because the payroll clerk simply forgets to do so. Consequently, Mr. Clark Johnson keeps getting paid.

    With good database design, referential integrity relationship would have been defined on these tables. They would have been linked and, depending on the defined constraints, the deletion of Clark Johnson from the employee table could not be performed unless it was accompanied by the deletion of Clark Johnson from the payroll table.

    Besides data integrity and data consistency, referential integrity also has the benefits listed in the following table.

     

    Benefit

    Description

    Increases development productivity

    It is not necessary to code SQL statements to enforce referential constraints.

    Teradata Database automatically enforces referential integrity.

    Requires fewer programs to be written

    All update activities are programmed to ensure that referential constraints are not violated.

    Teradata Database enforces referential integrity in all environments.

    No additional programs are required.

    Improves performance

    Teradata Database chooses the most efficient method to enforce the referential constraints.

    Teradata Database can optimize queries based on the fact that there is referential integrity.

    Rules for Assigning Columns as FOREIGN KEYS

    The FOREIGN KEY columns in the referencing table must be identical in definition with the keys in the referenced table. Corresponding columns must have the same data type and case sensitivity.

  • For standard referential integrity, the COMPRESS option is not permitted on either the referenced or referencing column(s).
  • Column level constraints are not compared.
  • A one-column FOREIGN KEY cannot reference a single column in a multicolumn primary or unique key—the foreign and primary/unique key must contain the same number of columns.
  • Circular References

    References can be defined as circular in that Table A can reference Table B, which can reference Table A. In this case, at least one set of FOREIGN KEYS must be defined on nullable columns.

    If the FOREIGN KEYS in Table A are on columns defined as nullable, then rows could be inserted into Table A with nulls for the FOREIGN KEY columns. Once the appropriate rows exist in Table B, the nulls of the FOREIGN KEY columns in Table A could then be updated to contain non-null values which match the Table B values.

    References to the Table Itself

    FOREIGN KEY references can also be to the same table that contains the FOREIGN KEY.

    The referenced columns must be different columns than the FOREIGN KEY, and both the referenced and referencing columns must subscribe to the referential integrity rules.

    CREATE and ALTER TABLE Syntax

    Referential integrity affects the syntax and semantics of CREATE TABLE and ALTER TABLE. For more details, see “ALTER TABLE” and “CREATE TABLE” in SQL Data Definition Language.

    Maintaining Foreign Keys

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

    Teradata Database 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.
  • Referential Integrity and the ARC Utility

    The Archive/Recovery (ARC) utility archives and restores individual tables. It also copies tables from one database to another.

    When a table is restored or copied into a database, the Data Dictionary definition of that table is also restored. The Data Dictionary definitions of both the referenced (parent) and referencing (child) table contain the complete definition of a reference.

    By restoring a single table, it is possible to create an inconsistent reference definition in Teradata Database. When either a parent or child table is restored, the reference is marked as inconsistent in the Data Dictionary definitions. The ARC utility can validate these references after the restore is done.

    While a table is marked as inconsistent, no updates, inserts, or deletes are permitted. The table is fully usable only when the inconsistencies are resolved (see below). This restriction is true for both hard and soft (Referential Constraint) referential integrity constraints.

    It is possible that the user either intends to or must revert to a definition of a table which results in an inconsistent reference on that table. The Archive and Restore operations are the most common cause of such inconsistencies.

    To remove inconsistent references from a child table that is archived and restored, follow these steps:

    1 After archiving the child table, drop the parent table.

    2 Restore the child table.

    When the child table is restored, the parent table no longer exists. The normal ALTER TABLE DROP FOREIGN KEY statement does not work, because the parent table references cannot be resolved.

    3 Use the DROP INCONSISTENT REFERENCES option to remove these inconsistent references from a table.

    The syntax is:

       ALTER TABLE database_name.table_name DROP INCONSISTENT REFERENCES

    You must have DROP privileges on the target table of the statement to perform this option, which removes all inconsistent internal indexes used to establish references.

    For more information, see Teradata Archive/Recovery Utility Reference.

    Referential Integrity and the FastLoad and MultiLoad Utilities

    Foreign key references are not supported for any table that is the target table for a FastLoad or MultiLoad.

    For further details, see:

  • Database Design
  • Teradata FastLoad Reference
  • Teradata MultiLoad Reference