There are multiple ways to disable constraints in Oracle.
The "alter table" disable constrains syntax can be used to disable
constraints in Oracle
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:
for i in (select constraint_name, table_name from
execute immediate 'alter table '||i.table_name||'
disable constraint '||i.constraint_name||'';
or here is an alternate example of Oracle disable
select 'alter table '||table_name||' disable constraint '||constraint_name||';'
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:
Type varchar2Array is table of
varchar2(1000) index by binary_integer;
select constraint_name Bulk Collect
where table_name = :tab_name
If tabSQL.count > 0 Then
For numCount in tabCons.first .. tabCons.Last
Immediate 'alter table '||:tab_name||' disable constraint '||tabCons(numCount);
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.
View more of my notes related to disabling Oracle constraints here: