Checking integrity of deferred foreign keys in Oracle

Question

Deferred foreign keys are useful because Oracle checks the integrity of the foreign key constraint only on commit and not after each statement.

Although this deferred integrity checking is very useful, sometimes I'd like to check the integrity without executing commit. Is it possible somehow? (Without changing the constraints to non-deferred).

I'm using Oracle 10g.

Answer 1

Hi,

you can issue set constraints all immediate. It will fail if any deferred constraint is violated. In case of failure you won't lose your work: your transaction won't be rolled back.