Re: Disabling check constraints

Mike Towery wrote:
> DA Morgan,
>
> You might want to update the reference on your website that uses
> "COMMIT WORK" then since no one in Oracle writes commit work :)
>
> http://www.psoug.org/reference/transaction.html
>
> show autocommit
> CREATE TABLE t0 (
> testcol NUMBER);
> INSERT INTO t0 (testcol) VALUES (1);
> COMMIT;
> INSERT INTO t0 (testcol) VALUES (2);
> COMMIT WORK;
>
>
> - Mike
>
> DA Morgan wrote:

>> 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:
>>
>> 1. Drop all existing foreign keys and replace them with deferrable
>>     constraints.
>> or
>> 2. 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 

The syntax is correct. But as a matter of practice ... Oracle developers and DBA's do not write the default "WORK" any more than you would feel comfortable writing "SELECT ALL."

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