I am looking at changing all of the foreign key definitions to be deferrable (initially immediate). Then during a few scenarios performed by the application, set all foreign key constraints to be deferred (initially deferred) for that given transaction.
My underlying question/concern is "will this change have any adverse affects (on performance) during normal operations when the foreign keys are set to deferrable initially immediate" .vs. the foreign keys being defined as NOT DEFERRABLE.
I have read that there can be a difference in behavior/performance when a Primary Key/Unique Key is changed to deferred, due to assumptions the optimizer can or cannot make regarding whether the associated index is unique. But I have not found any negatives in regard to changing foreign key definitions to be deferrable.
Thanks,
Alan
