Batch referential integrity is enforced as an all-or-nothing operation rather than row-by-row, and is therefore less expensive to enforce than standard referential integrity.
The system need not build and maintain reference indexes, so system performance improves.
Disks need not store reference indexes, so disk storage is available for other needs.
See Sizing a Reference Index Subtable 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.
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 one integrity-breaching row.
This is no different from the case for standard RI because for most transactions, only one row is involved.
Large batch RI rollbacks can also occur with ALTER TABLE and CREATE TABLE statements whose referential integrity relationships do not verify.
Batch RI is best for tables whose normal workloads you can be 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.