Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Disabling check constraints

Re: Disabling check constraints

From: Valentin Minzatu <valentinminzatu_at_yahoo.com>
Date: 17 Oct 2006 07:46:29 -0700
Message-ID: <1161096389.840533.134930@m73g2000cwd.googlegroups.com>


Alex,

In the title of your post you asked for check constraints to be disabled only. You can easily change the WHERE clause to adjust it to what you need (i.e. if you need to disable foreign key constraints as well, then the WHERE clause can look like: WHERE constraint_type IN ('C', 'R') ).

Cheers,
Valentin

On Oct 16, 10:41 am, "Alex" <alexadr..._at_virgilio.it> wrote:
> Valentin Minzatu ha scritto:
>
>
>
>
>
> > You can also do it dinamically, using "execute immediate":
> > BEGIN
> > FOR c IN (SELECT table_name, constraint_name FROM user_constraints
> > WHERE constraint_type = 'C' )
> > LOOP
> > EXECUTE IMMEDIATE 'ALTER TABLE '||c.table_name||' DISABLE
> > CONSTRAINT '||c.constraint_name;
> > END LOOP;
> > END;
> > /
>
> > More details about execute immediate can be found in "PL/SQL Guide and
> > Reference".
>
> > Cheers,
> > ValentinI tried it but it seems to have no effect on the problem:
>
> 1 BEGIN
> 2 FOR c IN (SELECT table_name, constraint_name FROM
> user_constraints
> 3 WHERE constraint_type = 'C' )
> 4 LOOP
> 5 EXECUTE IMMEDIATE 'ALTER TABLE '||c.table_name||' DISABLE
> 6 CONSTRAINT '||c.constraint_name;
> 7 END LOOP;
> 8* END;
> SQL> /
>
> Procedura PL/SQL completata correttamente. (successfully completed)
>
> SQL> update entity_fields set id_entity=id_entity + 10000;
> update entity_fields set id_entity=id_entity + 10000
> *
> ERRORE alla riga 1:
> ORA-02291: restrizione di integrità violata (DDS.ENTITYFIELDS) -
> chiave madre
> non trovata
>
> SQL> update entity set id_entity=id_entity + 10000;
> update entity set id_entity=id_entity + 10000
> *
> ERRORE alla riga 1:
> ORA-02292: restrizione di integrità violata (DDS.ENTITYFIELDS) -
> chiave figlia
> trovata
>
> What is wrong?- Hide quoted text -- Show quoted text -
Received on Tue Oct 17 2006 - 09:46:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US