Oracle SQL Developer - Disabling all constraints from schema

Question

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.

It isn't sufficient to run the 2 queries, you then need to run all the alter table statements that these have generated! — Tony Andrews
– Tony Andrews, Commented Sep 3, 2021 at 12:23
See my answer below. Or you can just copy the output from the selects and paste it into the command buffer to run them. — Tony Andrews
– Tony Andrews, Commented Sep 3, 2021 at 12:40
Ohh now I understand that it actually just prints out the queries that I have to run! Hmm, but the FK query does not print anything, any idea why? The 2nd one works fine. — willberthos
– willberthos, Commented Sep 3, 2021 at 12:46
It sounds like either there are no 'R' constraints in your schema, or they were already disabled when you ran the query. Try it again leaving out the and status = 'ENABLED' line (just to see what it finds). — Tony Andrews
– Tony Andrews, Commented Sep 3, 2021 at 12:48

Tony Andrews · Accepted Answer · 2021-09-03 12:30:03Z

3

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;

answered Sep 3, 2021 at 12:30

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

willberthos Over a year ago

can this be used to also enable the constraints back, by switching the status clause?

Tony Andrews Over a year ago

Yes, but then you would also change the order by so that 'R' constraints are done last.

willberthos Over a year ago

so order by case constraint_type when 1 then 2 else 'R' end ?

William Robertson Over a year ago

@willberthos something like that, but there is no constraint type '1'. See docs.oracle.com/en/database/oracle/oracle-database/19/refrn/… for the full list.

Tony Andrews Over a year ago

@willberthos no, order by case constraint_type when 'R' then 2 else 1 end

|

Collectives™ on Stack Overflow

Oracle SQL Developer - Disabling all constraints from schema

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related