Oracle Disable Constraints

Oracle Database Tips by Donald Burleson

There are multiple ways to disable constraints in Oracle.

The "alter table" disable constrains syntax can be used to disable constraints in Oracle

alter table
table_name
DISABLE constraint
constraint_name;

Another way to enable and disable constraints in Oracle would be to either use a plsql block or write a script.

Here is a script that makes use of Oracle disable constraint:

begin
for i in (select constraint_name, table_name from user_constraints) LOOP
execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;
/

or here is an alternate example of Oracle disable constraints:

select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;

Be careful when disabling constraints in Oracle, because of the implicit commits in DDL statements like this, sometimes you will find it necessary to "cache" the results of the cursor before starting to execute the DDLs used to disable constraints and avoid "snapshot too old" errors. This only happen occasionally, but if it does use this method to disable constraints in Oracle:

Declare
is
Type   varchar2Array is table of varchar2(1000) index by binary_integer;
tabCons mypkg.varchar2Array;
begin
select constraint_name Bulk Collect Into tabCons
from
(
select constraint_name
from   user_constraints
where table_name = :tab_name
);
If   tabSQL.count > 0 Then
      For   numCount in tabCons.first .. tabCons.Last
      Loop
          Execute Immediate 'alter table '||:tab_name||' disable constraint '||tabCons(numCount);
      end loop;
End If;
End;

The last way to disable constraints in Oracle would be to create a procedure to disable constraints. Prior to using this method to disable constraints all foreign keys must be disabled. An error would result from using this method to disable constraints if a pk is disabled while an fk is referring to it.

Oracle disable constraint