Re: Disabling check constraints

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 17 Oct 2006 08:09:21 -0700
Message-ID: <1161097757.3044@bubbleator.drizzle.com>

Alex wrote:
> Thomas Kellerer ha scritto:
>

>> On 17.10.2006 09:58 Alex wrote:
>>> Oracle version is 9.2 but I'm looking for something that works with any
>>> version...
>>> I need to update some key values on tables that have Foreign Key
>>> Constraints.
>>> What should I do?
>>>
>>> I don't know exactly what's the problem... DBMS returns this error
>>> message: "integrity constraint violated - child record found" when I
>>> try to update the key value on a parent table. So I want to disable
>>> that I call "check" on foreign key constraints.
>> What about my suggestion about setting the constraints to
>> deferrable/initially deferred, so that they will be validated at the end
>> of the transaction?
>>
>> Thomas

>
> I'm using SQL*Plus... I tried it but returns the same error.
> I've read something about "begin work" & "commit work"... but don't
> works anyway.
>
> SQL> begin work;
> 2 SET CONSTRAINTS ALL DEFERRED;
> 3 update entity set id_entity=id_entity+10000;
> 4 commit work;
> 5 /
>
> What I have to write?

Thomas missed one step in explaining it to you. You must initially create the constraints as deferrable. My recommendation would be one of the following:

Drop all existing foreign keys and replace them with deferrable constraints. or
Add the new values to the parent tables Then do your updates Then delete the old values from your parent tables.

From your above snippet it is obvious that you are trying to move into Oracle a skill set likely learned in SQL Server or Sybase. Pick up copies of Tom Kyte's books and read them: No one, in Oracle, writes COMMIT WORK.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Tue Oct 17 2006 - 10:09:21 CDT