15.00 - Standard Referential Integrity and Batch Referential Integrity - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Standard Referential Integrity and Batch Referential Integrity

In standard referential integrity, whether you are doing row-at-time updates or set-processing INSERT … SELECT requests, each child row is separately matched to a row in the parent table, one row at a time. A separate SELECT request against the parent table is performed for each child row. Depending on your demographics, Teradata Database might select parent rows more than once.

With batch referential integrity, all of the rows within a single request, even if only one row is affected, are spooled, sorted, and their references checked in a single operation, as a join to the parent table. Depending on the number of rows in the INSERT … SELECT request, batch referential integrity could be considerably faster, compared to checking each parent-child relationship individually.

For row-at-time updates, there is very little difference between standard referential integrity and batch referential integrity. But if you plan to load primarily using INSERT … SELECT requests, batch referential integrity is recommended.