Re: Oracle does not rollback correctly after ENABLE CONSTRAINT failed

On 5 feb, 16:52, uha..._at_gmail.com wrote:
> Hi,
> I run into a very weird behavier of Oracle server.
> After a failed attempt to enable foreign key the rollback did not
> return to the original database state.
>
> Example:
> I have two tables, in one (EMPL) there is a foreign key to the outher
> (DEPT).
> I have disabled the constraint and then delete all the rows in the
> DEPT table.
> Then I tried to enable the foreign key constraint and it failed (as it
> should), but when attempt to rollback it did not return to the
> original state.
>
> Here is a smal SQL script that ilustrate the problem:
> drop table empl;
> drop table dept;
> CREATE table dept(id integer not null, name varchar(25) not null);
> CREATE table empl(id integer not null, name varchar(25) not
> null,dept_id integer not null);
> ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY (id);
> ALTER TABLE empl ADD CONSTRAINT empl_pk PRIMARY KEY (id);
> ALTER TABLE empl ADD CONSTRAINT empl_dept_fk FOREIGN KEY(dept_id)
> REFERENCES dept;
> INSERT INTO dept VALUES (1,'dept 1');
> INSERT INTO empl VALUES (1,'empl 1',1);
> COMMIT;
>
> SELECT DEPT.NAME,EMPL.NAME FROM empl LEFT OUTER JOIN dept ON EMPL.ID =
> DEPT.ID;
> ALTER TABLE EMPL DISABLE CONSTRAINT EMPL_DEPT_FK;
> DELETE FROM DEPT;
> SELECT DEPT.NAME,EMPL.NAME FROM empl FULL OUTER JOIN dept ON EMPL.ID =
> DEPT.ID;
> ALTER TABLE EMPL ENABLE CONSTRAINT EMPL_DEPT_FK;
> ROLLBACK;
> SELECT DEPT.NAME,EMPL.NAME FROM empl LEFT OUTER JOIN dept ON EMPL.ID =
> DEPT.ID;
>
> quit;
>
> Well friends, Any ideas
> Uri

DDL always do an implicit COMMIT. It is done BEFORE the DDL. So, it doesn't matter if the DDL succeeds or fails: the commit is done (no chance for a rollback then).

HTH. Cheers.

Carlos. Received on Mon Feb 05 2007 - 10:49:39 CST