Download (direct link):
For each column-level REFERENCES clause:
IF you ... THEN .
omit column_name the Parent table must have a single-column primary key; the FK column references that column by default.
specify column_name the name must reference: • The single-column primary key of the Parent table • A single-column alternate key in the Parent table, defined as UNIQUE
Standard referential integrity checks the equality row by row, ensuring data integrity during insert, delete, or update operations on a table. However, this fine granularity incurs a modest performance overhead.
Also, referential integrity checking is not supported at all for use with:
• COMPRESS values
• Identity columns
• Column-level constraints (other than reference) on PK and FK columns
• Global temporary and volatile tables
If such operations are attempted on a Child or Parent table, Teradata RDBMS issues an error message.
Note: A complete explanation of the key columns and how to choose them and the referential integrity rules and how to apply them are given in "FOREIGN KEY" and "REFERENCES" under CREATE TABLE and ALTER TABLE in Teradata RDBMS SQL Reference, Volume 4.
7 - 10
Teradata RDBMS Database AdministrationChapter 7: Protecting Data
Using Referential Integrity
Batch Referential Integrity Constraint
A batch referential integrity constraint is less expensive to enforce than standard referential integrity because checking is performed on qualified rows within a transaction rather than on all rows in the table.
When the reference point is reached, the parser joins the Parent and Child rows and then tests them. If a violation is found, all statements in the transaction are rolled back. Thus, the enhanced performance can incur the following costs:
• With very large tables, a rollback can an expensive. You should use Batch RI only for smaller tables, or those whose normal workloads will not trigger reference violations.
• Query results might be inaccurate, depending on the type and amount of operations in the transaction and how deeply into the transaction the first violation is detected.
• Utilities like FastLoad and MultiLoad are not able to operate on tables defined for referential integrity checking.
Referential Constraint Using WITH NO CHECK OPTION
No constraints are enforced when you use the WITH NO CHECK OPTION with the REFERENCES clause in the CREATE TABLE or ALTER TABLE statement. Referential Constraint allows referential integrity to be used to advantage by the Optimizer, but does not incur the overhead of the database-enforced referential integrity.
Warning: WITH NO CHECK OPTION does not enforce the referential constraints. It is possible, then, that incorrect results and/or corrupt data could occur, unless you take other measures to ensure that data integrity is maintained. You should allow NO CHECK only when the possibility of data corruption or deletion and erroneous query responses are not critical or can be prevented. (For full details and examples, see "Validating the Integrity of Tables in a Referential Constraint Relationship" and "Scenario for Data Corruption With Referential Constraint" in Teradata RDBMS SQL Reference, Volume 4.)
Use care when manipulating data within a NO CHECK environment. NO CHECK means that a row having a non-null value in a FK column is allowed to exist in a Child table when an equal value does not exist in the PK or alternate column of the Parent table. Also, DML operations are allowed on NO CHECK tables that cannot be performed on referential integrity tables.
For example, if a referential relationship is defined using NO CHECK, and an INSERT, DELETE, or UPDATE statement containing a redundant RI join is applied when the PK-FK row pairs for the eliminated join do not match, the operation is allowed.
Data in the Parent tables of these relationships can be deleted or corrupted. Depending on the operation, no warning is given if such an error occurs.
7 - 10 Teradata RDBMS Database Administration
Chapter 7: Protecting Data
Using Referential Integrity
Operational behavior for RI with WITH NO CHECK OPTION is as follows:
IF you use NO CHECK tables with . THEN .
FastLoad and MultiLoad • Reference violations are not checked • Flags are not set, warning are not issued, and error messages are not returned if violations exist
INSERT, UPDATE, and DELETE processing • Reference violations are not checked • Flags are not set, warning are not issued, and error messages are not returned if violations exist
ARC there is no change in operation, behavior, or any RI-related error messages. You can use the REVALIDATE REFERENCES FOR command to reset the state of the table after a RESTORE operation.
CheckTable CheckTable ignores constraints on NO CHECK tables and sends a message that RI checks are being skipped. Otherwise, there is no change in operation or behavior.