Re: Disabling check constraints

From: Mike Towery <mtowery_at_gmail.com>
Date: 18 Oct 2006 07:11:35 -0700
Message-ID: <1161180695.718713.8970@m7g2000cwm.googlegroups.com>

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
Received on Wed Oct 18 2006 - 09:11:35 CDT