Oracle does not rollback correctly after ENABLE CONSTRAINT failed

From: <uhaham_at_gmail.com>
Date: 5 Feb 2007 07:52:20 -0800
Message-ID: <1170690740.923062.251900@k78g2000cwa.googlegroups.com>

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 Received on Mon Feb 05 2007 - 09:52:20 CST