I'm managing quite an intricate Oracle relational database (~50 tables) with many integrity constraints in place. All PKs and FKs are NOT DEFERRABLE, I would like to change them to DEFERRABLE INITIALLY IMMEDIATE, but I understand my only chance is dropping and recreating all of them.

This becomes a nightmare quickly, as I should drop all FKs first not to break integrity, then the same for all PKs and then recreating all in opposite order.

Is there a better way to do this, or I'm being unable to see the easy solution under my nose?

CodePudding user response：

SQL Language Reference for Oracle 21c states that

Modifying constraints is subject to the following restrictions:

• You cannot change the state of a NOT DEFERRABLE constraint to INITIALLY DEFERRED."

So there is no simple solution. You have to drop and recreate constraints. If you are afraid of other sessions interfering with data then you could block them. There are multiple ways of doing it (for ex. ALTER SYSTEM ENABLE RESTRICTED SESSION; - be aware that in that mode only SYS and users with RESTRICTED SESSION will be allowed to log in).

The second approach will be using a DBMS_REDEFINITION to "redefine" tables and especially constraints. It consist of few steps like:

1. creating a interim tables - similar to original ones but with FK which is DEFERRABLE INITIALLY IMMEDIATE - you only need to create that one FK which is different - others can be copied in step 3.
2. Start the redefinition process with DBMS_REDEFINITION.START_REDEF_TABLE.
3. Copy dependent objects with DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS.
4. Check DBA_REDEFINITION_ERRORS view for errors - there should be the one with existing FK - which is OK, because that FK has been created in step 1.
5. Synchronize the interim table: DBMS_REDEFINITION.SYNC_INTERIM_TABLE.
6. Finish the redefinition process DBMS_REDEFINITION.FINISH_REDEF_TABLE.
7. Drop the interim table (you might have to wait for any long-running queries).

The main advantage of DBMS_REDEFINITION is that this operation is online and makes it almost transparent for users. On the other hand, it is even more complicated.

CodePudding user response：

It's not difficult to query the information schema and list all 6 types of constraints (not null, check, pk, fk, unique, ref), specially if all of them reside in the same schema.

This way, you can run 6 separate queries to produce the "drop constraints" script, and another 6 separate queries to produce the "create constraints" script (with your modifications).

This should be fairly simple to do.