Copy Tables with Referential Integrity | Teradata Data Mover - 17.10 - About Copying Tables with Referential Integrity - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.10
Release Date
June 2021
Content Type
User Guide
Publication ID
B035-4101-061K
Language
English (United States)

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 DSA.

A table has referential integrity if any of its foreign key (FK) columns reference primary key (PK) columns in another table. Referential integrity helps make sure 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.
Source Table Target Table Data Mover Results
FK definition exists Table does not exist. Copies the source table to the target with the FK definition of the source.
FK definition exists 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 when copying source tables A and B where 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.
  • 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 if 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 DSA, 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 only be copied without copying its parent table only if the child table already exists on the target database, or if 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, the tables can be moved only with Teradata DSA. Partial copies are not supported for this type of RI table with DSA, TPT, or JDBC.

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.