Combining 3 steps into one SQL script

Question

I'm pretty new to SQL, so it sould be rather easy to answer my questions.

Here is what I want to do:

Deactivate constraints:

Deactivate constraints in the database:

begin
    for cur in (select fk.owner, fk.constraint_name , fk.table_name 
           from all_constraints fk, all_constraints pk 
           where fk.CONSTRAINT_TYPE = 'R' and 
                 pk.owner = 'USER1' and
                 fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME ) loop
        execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT '||cur.constraint_name||' DISABLE';
    end loop;
end;

Delete from tables:

delete from USER_TEST.Table1;                
delete from USER_TEST.Table2;               
delete from USER_TEST.Table3;

Reactivate Constraints:

begin
  for cur in (select fk.owner, fk.constraint_name , fk.table_name 
              from all_constraints fk, all_constraints pk 
              where fk.CONSTRAINT_TYPE = 'R' and 
                    pk.owner = 'USER1' and
                    fk.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME ) loop
       execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' MODIFY CONSTRAINT '||cur.constraint_name||' ENABLE NOVALIDATE';
   end loop;
end;

Does anyone know how to combine these steps into one .sql script so I can run this on Oracle SQLDeveloper? Or maybe a more elegant way to perform the deletion from the tables?

I'd be very thankful

Take out the last 'end;' from 1. and the first 'begin' from 3 ? — Hugh Jones
– Hugh Jones, Commented Jan 25, 2013 at 13:08
Be very careful, though - you have the potential risk of being unable to reactivate constraints after 2. — Hugh Jones
– Hugh Jones, Commented Jan 25, 2013 at 13:11
You could order the deletes so that you delete in the correct order ("children" first, "parents" last). Or declare the FK constraints as "DEFERRABLE" in that case they will be evaluated when you commit, not when you run the DELETE — user330315
– user330315, Commented Jan 25, 2013 at 13:16
The issue is not so much the way you are deleting so much as THAT you are deleting. I suppose you are removing the constraints just to speed things up, but it is easy to imagine a foreign key that cannot be re-enabled after data have been removed... — Hugh Jones
– Hugh Jones, Commented Jan 25, 2013 at 13:34
You can define the constraints as deferred, then you don't need to bother about the correct order and the constraints will be evaluated when you commit the DELETE transaction. — user330315
– user330315, Commented Apr 20, 2013 at 16:33

Martin Drautzburg · Accepted Answer · 2013-04-20 11:53:13Z

If it is just about deleting from tables which have foreign keys to each other, you can always delete in constraint order ("children first"). Then you won't have to mess with your constraints at all. Don't forget to commit at the end.

If it is about speed, then you may want to disable constraints and empty the tables via TRUNCATE rather then DELETE. You should however, not delete ALL constraints of the schema, but just the foreign keys of the affected tables which point to another affected table. This will prevent you from shooting into your foot. You can to this without looping, just disable the constraints explicitly. E

So the entire script should look like this

Alter Table User_test.Table1 Modify Constraint FK... Disable;
Alter Table User_test.Table2 Modify Constraint FK... Disable;
...

Trunacte Table user_test.Table1;
Trunacte Table user_test.Table2;
...

Alter Table User_test.Table1 Modify Constraint FK... Enable;
Alter Table User_test.Table2 Modify Constraint FK... Enable;
...

Collectives™ on Stack Overflow

Combining 3 steps into one SQL script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related