Archives of the TeradataForum

Message Posted: Mon, 23 Jun 2003 @ 22:13:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Monday, June 23, 2003 14:54 -->

Hello,

Can somebody please explain to me the difference between batch referential integrity and standard referential integrity?

From the description of them, they appear different, but in practice they appear to do the same thing. From the manuals:

Standard:

- Tests each individual inserted, deleted, or updated row for referential integrity.

- If insertion, deletion, or update of a row would violate referential integrity, then AMP software rejects the operation and returns an error message.

Batch:

- Tests an entire insert, delete, or update batch operation for referential integrity.

- If insertion, deletion, or update of any row in the batch violates referential integrity, then parsing engine software rolls back the entire batch and returns an abort message.

Lets say that I had a table called X with some number of rows and I wanted to insert these rows into table Y (insert into X select * from y). However, some of the rows violated an RI constraint that table Y had. From reading the manuals, it seemed to me that if using standard RI, all of the valid rows would be inserted but the invalid ones would not. But with batch RI (which is "all or nothing") I would expect nothing to get inserted since it would check for problem rows up front and return an error right away.

However, in my tests, both standard and batch RI have the same results... no rows inserted. Now, prior to reading about batch RI, I would have assumed that the standard one would fail outright (i.e. be "all or nothing" -- which it does seem to be doing) but I thought that this couldn't be the case given the similar discription that batch RI has.

If in fact there is no difference except in how Teradata processes things internally (i.e. where it checks for invalid rows) then why would you want to use one over the other? Wouldn't you always want to use batch since it does the checking up front and saves processing time?

Thanks a lot!

	<Prev	Next>		<<First	<Prev	Next>	Last>>