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!