15.00 - Batch Referential Integrity Constraints - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Batch Referential Integrity Constraints

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 Database Design 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.

    Note that 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 requests 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.