And here are the results. Two modes raised the error during backout and 2 succeeded.
NOCASCADE Raised Error NOCASCADE_FORCE Raised Error CASCADE No Error 2 Records Afterwards DEMO_ID DEMO_NAME ---------- --------- 2 Demo 2 1 Demo 1 SQL Executed delete from DRH.DEMO where DEMO_ID = '1' and DEMO_NAME = 'Demo 1' insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values ('2','Demo 2') insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values ('1','Demo 1') Explanation Because this is cascade mode it starts with the latest transaction - the re-insert of record 1. This is backed out with the delete SQL. Then the delete transaction is backed out with 2 inserts. NONCONFLICT_ONLY No Error 2 Records Afterwards DEMO_ID DEMO_NAME ---------- --------- 1 Demo 1 2 Demo 2 SQL Executed insert into DRH.DEMO(DEMO_ID,DEMO_NAME) values ('2','Demo 2') Explanation This much simpler - it just inserts the row with DEMO_ID=2.
How many did you get right?
Thanks for reading!