Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    13

    Unanswered: Remove Delete Cascade Constraints or Recreate Constraints

    hey,

    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

    Thanks!

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    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'
    then
    v_delete_rule := 'ON DELETE CASCADE';
    end if;

    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';

    HTH
    Gregg
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •