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