About Copying Tables with Referential Integrity - Teradata Data Mover

Teradata Data Mover User Guide

Product
Teradata Data Mover
Release Number
16.00
Published
December 2016
Language
English (United States)
Last Update
2018-03-29
dita:mapPath
rdo1467305237457.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-4101
lifecycle
previous
Product Category
Analytical Ecosystem

In most cases, you can copy tables that have referential integrity from the source to the target using Teradata PT API, Teradata JDBC, and Teradata ARC.

A table has referential integrity if any of its foreign key (FK) columns reference primary key (PK) columns in another table. Referential integrity helps ensure data integrity and consistency between PK and FK columns.

Referential integrity constraints can be added to a table when using CREATE TABLE or ALTER TABLE statements. In the Teradata Database, you can declare standard, batch, and soft referential integrity. In the following examples of the different types of referential integrity, the composite foreign key column-set (Name, Deptid) references the primary key columns (Name, Deptid) in the parent table db.employee_PK:
Referential Integrity Example Description
Standard
FOREIGN KEY ( Name , Deptid ) REFERENCES 
db.employee_PK ( Name , Deptid ))

Enforces referential integrity at the row level.

Batch
FOREIGN KEY ( Name , Deptid ) REFERENCES WITH 
CHECK OPTION db.employee_PK ( Name , Deptid ))

Enforces referential integrity for an entire implicit transaction; useful when a single statement inserts multiple rows into a table.

Soft
FOREIGN KEY ( Name , Deptid ) REFERENCES WITH 
NO CHECK OPTION db.employee_PK ( Name , Deptid ))

Explicitly instructs the Teradata Database not to incur system overhead by enforcing referential integrity on the PK-FK relationship.

The following scenarios describe how Data Mover handles copying tables with foreign keys.
Foreign Key Description Data Mover Results
FK definition Does not exist. Copies the source table to the target with the FK definition of the source.
FK definition Has no FK definition. Copies the source table to the target with no referential integrity, therefore the target table has no FK definition.
FK defined for col1 and col2 Has FK defined for col3 and col4. Copies the source table to the target but the target table keeps its original FK definition (FK on col3 and col4). The target table does not use the FK definition of the source table (FK on col1 and col2).
The following restrictions apply to copying tables that have referential integrity:
  • If a parent table is copied to the target, the table cannot exist on the target with a child that is not copied as part of the job for either of the following two conditions:
    Condition Example Error Information
    Child has hard referential integrity to the table. Source tables A and B, where A is the parent of B and C. An error occurs if you copy source tables A and B because C has hard references to A on the target, but C is not being copied. The job should be created when only moving A and C, because B is soft referential integrity to A.
    Target tables A, B, and C, where A is the parent of B and C, B has soft referential integrity reference to A, and C has hard referential integrity references to A.
    Parent table has at least one child hard reference on its Generate Always columns, and target system is below 14.10. All target children need to be moved together in the job. Source tables A, B and C, where A is the parent of B and C. An error occurs if you copy source tables A and B, but C is not being copied.
    Target tables A, B, and C, where A is the parent of B and C, B hard reference to A's Generate Always columns, but C is Soft Reference to A.
    Target system is below 14.10.
  • When copying a child table, the parent table can only be renamed or relocated if the child table already exists on the target database, or the parent table exists in the same database on the target as it does in the source.
  • If the table does not exist on the target, the source table definition is used to create the table on the target. Because the Teradata Database represents the primary key as a unique index in a table definition, the target table is created with a unique index instead of a primary key. Child tables can reference tables with a unique primary or secondary index.
  • If a job copies the entire database with Teradata ARC, and the database has tables with foreign keys, the target tables are created without foreign keys.
  • If the source and target tables have different foreign keys, the foreign keys on the target table are maintained after the table copy.
  • A child table can be copied without copying its parent table only if the child table already exists on the target database, or the parent table exists in the same database on the target as it does in the source.
  • When moving tables, if a parent table has a GENERATE ALWAYS identity key and that key is used as a foreign key for a child table, they can be moved only with Teradata ARC. Partial copies are not supported for this type of RI tables with ARC, TPT, or JDBC.
  • When moving referential integrity reference tables with hard referential integrity reference on Generate Always columns and the target system is below 14.10, the moved tables cannot have nested referential integrity relationship on Generate Always columns. Nested referential integrity relationship on Generate Always columns: tables A and B and C, where A is the parent of B, and B is parent of C. Both A and B have Generate Always columns. B references to A's Generate Always columns as FK, and C references to B's Generate Always columns. This is called nested RI relationship on Generate Always columns.
    • Job to move A, B, C will cause validation error when A, B, C do not exist on target and the target system is below 14.10.
    • Job to move A, B, C will cause validation error when A, B, C already exist on target, but have nested referential integrity relationship on Generate Always columns on target, and the target system is below 14.10.
  • When moving a parent table that already exist on the target, and its Generate Always columns are hard referenced by a child on target system, and the target system is below 14.10, all the children and grandchildren on the target system need to move together with this parent; otherwise, a validation error will occur.
  • When a parent table has Generate Always columns hard referenced by a child on target system and it is also a child of another table, a job to move this table and its children will cause a validation error if the target system is below 14.10.

Two tables are said to have circular references if a primary key in the parent table (PT1) references a foreign key in the child table (CT1) and a primary key in CT1 references a foreign key in PT1. That is, a mutual PK-FK relationship exists between both tables. When copying source tables with circular references, Data Mover can only copy to the target if the target tables have no mutual PF-FK relationship; there must not be any circular references between the target tables. An error occurs if tables do not exist on the target when Data Mover tries to copy source tables with circular references.