Disable all constraints of a table

Disable all constraints of a table

MARCH 17 2011, BY SIMON KRENGER

During maintenance, I had to disable all constraints of a table. I knew that Oracle SQL Developer (I really like it even though it is a developer tool and not aimed at Database Administrators) had built-in functions to do this, but since I could only access the database machine via SSH, I had to do it in SQL*Plus.

Luckily, SQL Developer is quite transparent about the commands it uses and I could therefore easily see what is going on when you disable all constraints on a table using the GUI. So here it goes…

Disable all constraints of a table:

begin
 for cur in (select owner, constraint_name , table_name 
  from all_constraints
  where owner = 'SIMON' and
  TABLE_NAME = 'MY_TABLE') loop
   execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||' 
   MODIFY CONSTRAINT "'||cur.constraint_name||'" DISABLE ';
   end loop;
end;
/

Remember to enable the constraints again before ending maintenance or else you might end up with a logically inconsistent database:

begin
 for cur in (select owner, constraint_name , table_name 
  from all_constraints
  where owner = 'SIMON' and
  TABLE_NAME = 'MY_TABLE') loop
   execute immediate 'ALTER TABLE '||cur.owner||'.'||cur.table_name||'
   MODIFY CONSTRAINT "'||cur.constraint_name||'" ENABLE ';
   end loop;
end;

http://www.krenger.ch/blog/disable-all-constraints-of-a-table/