CODEWITHSUNDEEP
sqldatabaseoracle-databaseconstraints
willberthos
willberthos

Reputation: 315

Oracle SQL Developer - Disabling all constraints from schema

I have two queries for disabling all constraints, but they don't seem to be working.

first one disables foreign keys:

select 'alter table '||table_name||' disable constraint '||constraint_name||';'
from user_constraints
where constraint_type ='R'
and status = 'ENABLED';

and the second disables everything else:

select 'alter table '||table_name||' disable constraint '||constraint_name||';'
from user_constraints
where status = 'ENABLED';

Now when I check the constraints with SELECT * FROM USER_CONSTRAINTS I can see that they all are still 'ENABLED'. Why is this? I tried commit after running the queries but to no avail.

My goal is to disable constraints from all tables with those queries.

Upvotes: 0

Views: 10797

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132570

As per my comment above, it isn't sufficient to run the 2 queries, you then need to run all the alter table statements that these have generated. However you could do it all at once using PL/SQL. I have combined the 2 queries into one, using order by to process the foreign keys (constraint_type = 'R') first:

begin
  for r in
    ( select 'alter table '||table_name||' disable constraint '||constraint_name as statement
      from user_constraints
      where status = 'ENABLED'
      order by case constraint_type when 'R' then 1 else 2 end
    )
  loop
    execute immediate r.statement;
  end loop;
end;

Upvotes: 3

Related Questions