Re: Disabling check constraints

From: Thomas Kellerer <TAAXADSCBIXW_at_spammotel.com>
Date: Mon, 16 Oct 2006 16:51:12 +0200
Message-ID: <4phkj1Fir6n3U1@individual.net>

On 16.10.2006 16:41 Alex 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,
>> Valentin

>
> I 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?

Because you only disabled the CHECK constraints but not foreign key constraints. These are two different types.

Is this a one time thing, or do you need to do that on a regular basis? If the latter, you could declare your constraints (FK and Check) to be deferrable and maybe even initially deferred. That way you don't need to disable them. When you define all of them as deferrable you can simply defer the check until commit using:

SET CONSTRAINTS ALL DEFERRED; For details see:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_104a.htm#2066962 and
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/clauses3a.htm#998196

Thomas Received on Mon Oct 16 2006 - 09:51:12 CDT