Batch Referential Integrity Constraints - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

Batch referential integrity is a form of referential integrity checking that is less expensive to enforce in terms of system resources than standard referential integrity because it is enforced as an all-or-nothing operation rather than on a row-by-row basis, as standard referential integrity is checked.

Batch RI also conserves system resources by not using reference indexes. Eliminating the use of reference indexes has the following effects.
  • System performance is enhanced because the system does not need to build and maintain them.
  • Disk storage is saved because the space that would otherwise be used to store them is instead available for other storage needs.

See Teradata Vantage™ - Database Design, B035-1094 for further information about reference indexes.

Batch referential integrity relationships are defined by specifying the WITH CHECK OPTION phrase for a REFERENCES constraint. When you specify this phrase, the database enforces the defined RI constraint at the granularity of a single transaction or request.

This form of RI is tested by joining the relevant parent and child table rows. If there is an inconsistency in the result, then the system rolls back the entire transaction in Teradata session mode or the problem request only in ANSI session mode.

In some situations, there can be a tradeoff for this enhanced performance because when an RI violation is detected, the entire transaction or request rolls back instead of 1 integrity-breaching row.

In many instances, this is no different than the case for standard RI because for most transactions, only 1 row is involved.

There is a significant potential for performance problems when updating tables with batch RI constraints in situations where multiple update operations are involved: for example, an INSERT … SELECT or MERGE operation involving thousands or even millions of rows. This would be an expensive operation to have to roll back, cleanse, and then rerun.

Similar very large batch RI rollbacks can occur with ALTER TABLE and CREATE TABLE statements whose referential integrity relationships do not verify.

Because of its all-or-none nature, batch RI is best used only for tables whose normal workloads you can be very confident are not going to cause RI violations.

You cannot use bulk data loading utilities like FastLoad, MultiLoad, or Teradata Parallel Transporter to load rows into tables defined with batch referential integrity.

Batch referential integrity is a Teradata extension to the ANSI SQL:2011 standard.