Please let me know how to fix this error. How does the CHECK OPTION work in TRANSACTION mode?
Here is my scenario,
1. Say booking_number in table B is defined as foreign key which is the primary key of table A.
2. In my stored procedure, the same booking record is inserted into A and then B.
3. There is no error if the stored procedure is called without TRANSACTION mode.
4. There is also no error if the stored procedure is called with TRANSACTION mode and the foreign key was defined as "WITH NO CHECK OPTION"
5. The error only happens in TRANSACTION mode and the foreign key is defined as "WITH CHECK OPTION". It looks like in TRANSACTION mode, the booking_number was not considered already inserted into A so the CHECK failed while the booking_number is being inserted into B.
You have answered your question in the last line, when you use WITH CHECK OPTION , refrences are checked at transaction level. If you are inserting a row in the parent table and then the refrenced row in Child table in same transaction, it will not be succedded. as the the row inserted into A is not committed yet. To make this work, You will have to add two transactions to make this happen, or change it to soft RI WITH NO CHECK OPTION.
within the transaction the row will be visible without commiting it, the error must be caused by something else.#
What do you mean by TRANSACTION mode?
ANSI vs. Teradata session mode?
Which client/connection do you use to run the SP?
Could you show the SP source code?
sorry for the late response. I am using TRANSACTION in teradata session mode and I use Teradata Express Studio with TMODE=TERA connection on MAC. My stored procedure looks like this:
CALL DBName.DATA_IMPORT(dt, ErrorCode, ErrorMsg);
IF (ErrorCode = 0) THEN
REPLACE PROCEDURE DBName.DATA_IMPORT()
I have to either not use TRANSACTION or use WITH NO CHECK OPTION to make it work. You may create simple tables and try it out. Thanks!