Re: Disabling check constraints

On Mon, 16 Oct 2006 05:55:04 -0700, Alex wrote:

> Hi all,
> I want to be able to disable constraints, perform a number of updates,
> then re-enabling the constraints. I need to do this because some of the
> updates in a batch will break constraints rule
> during the batch but by the end everything should be consistent.
>
> I have to do it on an Oracle db and a SqlServer db, with SqlServer I
> currently have used this script:
>
> exec sp_MSforeachtable 'alter table ? nocheck constraint all';
> ...updates...
> exec sp_MSforeachtable 'alter table ? check constraint all';
>
> I'm looking for something like that which works with Oracle.
>
> Thanks in advance

alter table disable constraint <constraint name>. I am not aware of any option to disable all constraints on the table but in the brave new world of Oracle RDBMS, we use something called "SQL generating SQL" for that purpose.

You would have to write something like this:

set pagesize 0
set trimspool on
set trimout on
spool /tmp/dis_cons.sql
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints
where table_name=upper('&tab_name');
spool off
@/tmp/dis_cons

-- 
http://www.mladen-gogala.com