Deferrable foreign keys - any gotchas?

Hi there!

We have a system made up of ca 600 tables and 1200 foreign keys. We have primary key on all tables, and indexes on all foreign keys.

We want to take advantage of the possibility of having deferrable foreign keys - dropping them all and remaking them as 'deferrable initially immediate'
That way some of our routines (pl/sql packages) may set a couple of the FKs as deferred, do it's job, and then set them as immediate again (by calling dbms_sql-routines in a different package).

The reason why we hesitate is that we worry, mostly about locking but also about performance.

Will a deferrable FK that in this session is immediate, cause any different locking than a non-deferrable FK, when inserting, updating or deleting in the parent- or child-table?

How about when a routine sets a FK to be deferred, will inserting, updating or deleting in the parent- or child-table cause any different locking than a non-deferrable FK?

The next concern is efficiency - will deferrable FKs cause inserting, updating or deleting in the parent- or child-table to be slower than if the FKs are non-deferrable?

This is our configuration:
Oracle8 Enterprise Edition Release 8.0.5.1.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.5.1.0 - Production

Thanks in advance for any contributions!

Regards, Njål A. Ekern Received on Thu May 20 1999 - 17:30:20 CDT