Answer: Backout And Constraints

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!