Unanswered: Remove Delete Cascade Constraints or Recreate Constraints
Ok...I'm trying to remove the cascade delete from my constraints. Going by the searches I've done, it seems the only solution is to drop and recreate? Unless someone knows another way?
Assuming I need to recreate these constraints, does anyone know a quick and easy way to generate a script to do this? I looked at generating the statement from the user_constraints table, but it doesn't have all the details. I'm not too keen on manually recreating 100 of these buggers so any help will be greatly appreciated
Here's a script that will create a script for foreign keys to a table ... If you
don't want the delete cascade, look for the code below in the script and commit it out...
if Lv_Cons_Ref_Delete = 'CASCADE'
v_delete_rule := 'ON DELETE CASCADE';
Change the location of the spool file at the end of the script to match with your environment. It will prompt you for owner and table names ...
You can write a sql statement that will fill those in for all tables ....
(let sql do the work for you - Change the accepts to defines and pass variables)...
sql> select distinct '@c:\...\gen_fkey '||table_name||' schemaowner'
from user_constraints where constraint_type = 'R';