16.10 - Referential Integrity and the ARC Utility - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)

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.

Related Topics

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