Re: Oracle does not rollback correctly after ENABLE CONSTRAINT failed

On Feb 6, 3:27 am, uha..._at_gmail.com wrote:

> I will need to redesign this part, John suggestion sound
> resnable even that I have a lot of dependencies and I hoped the
> db server will do the checking

Hi, uhaham, it turns out there is a much better way, but it requires that the whole load be done in one (undo-able) transaction, which I think is your case. Oracle has "deferrable constraints" just for your situation. See: http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14220/data_int.htm#i4665

This approach will not require you to code (correctly!) the queries which "test" the constraints.

To make it work you need to:

• Drop & re-create your constraints, but adding the DEFERRABLE keyword at the end of each one you suspect may fail when re-enabled
• Before doing any INSERT/UPDATE/DELETE issue ALTER SESSION set constraints = deferred;
• Do your inserts, updates and deletes (no commits)
• Call COMMIT on the whole thing. If any constraint fails the whole thing will be rolled back, which I believe is just what you want
• Below a simple example you can try.

There are probably different school of thought on whether to use DEFERRABLE. Personally in my apps I try hard never to initiate an Oracle transaction that I could predict in advance would fail a constraint violation (e.g., duplicate primary key, etc.) and try to raise those errors within the app. However that requires a lot of work so there is a tradeoff.

Hope that is more helpful,
JH

-=-=-=-=-=-=-= Example -=-=-=-=-=-=

/* DEPT table */
create table dept (
  id number not null,
  name varchar2(100) not null,
  constraint pk_dept primary key (id) deferrable );
insert into dept values (1, 'Sales');
insert into dept values (2, 'Engineering'); commit;
/* EMPL table */
create table empl (
  name varchar2(100) not null,
  dept_id number not null,
  constraint pk_empl primary key (name) deferrable,   constraint fk_empl_d foreign key

             (dept_id) references dept (id) deferrable );
insert into empl values ('Lyan S. Dogg', 1); insert into empl values ('Jean E. Uss', 2); commit;
/* Do an "undoable bulk load" w/ constraints deferred */ alter session set constraints = deferred; /* These changes will violate constraint(s) */ delete from dept;
/* Show data in inconsistent state */
select * from dept;
select * from empl;
/* Try to commit - should generate an error and roll things back */ commit;
/* Show data back in consistent state */ select * from dept;
select * from empl;

-=-=-=-=-=-=-= Example -=-=-=-=-=-= Received on Tue Feb 06 2007 - 11:16:25 CST