torek, 31. januar 2012

Oracle 11g BUG when using deferred constraints

Found a BUG when using deferred constraints on Oracle DB version Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.

Here is the test case:
First we create two tables. Table P1 is the parent table, which has a primary key on column ID. The second table C1 is a table (child) with a foreign key constraint which is deferrable and references the parent table.

Create the two tables:

CREATE TABLE P1 (ID NUMBER);
CREATE TABLE C1 (ID NUMBER, X VARCHAR2(2));
Create the primary key and foreign key constraints:
ALTER TABLE P1 ADD CONSTRAINT PK_ID PRIMARY KEY (ID);
ALTER TABLE C1 ADD CONSTRAINT FK_P1_ID FOREIGN KEY (ID) REFERENCES P1 (ID) DEFERRABLE;
We insert same test data into the two tables:
INSERT INTO P1 VALUES (1);
INSERT INTO C1 VALUES (1,'X1');
INSERT INTO C1 VALUES (1,'X2');
Now we create the test to delete some data, while switching the constraints to deferred. We try to delete the "parent" record, and one delete one of the child records in the child table. We leave on child record. After the delete statements we switch back the constraints to immediate:
SET CONSTRAINTS ALL DEFERRED;

DELETE P1 WHERE ID = 1;
DELETE C1 WHERE X = 'X1';

SET CONSTRAINTS ALL IMMEDIATE;
When we execute the final statement (SET CONSTRAINTS ALL IMMEDIATE) we get an error ORA-02292: integrity constraint (schema.FK_P1_ID) violated - child record found.

This is what we expected and is of course correct.

But here is the trick. If we add an index to the table C1 on column ID something weird happens.
Lets create an index:
CREATE INDEX IND_1 ON C1 (ID);
Now we try the test again:
SET CONSTRAINTS ALL DEFERRED;

DELETE P1 WHERE ID = 1;
DELETE C1 WHERE X = 'X1';

SET CONSTRAINTS ALL IMMEDIATE;
After executing the last statement we would expect the same error as we got before, but we didn't receive any error. We corrupted the data and now have corrupt data in the database. If we now look at the data in the two tables, we can see that the parent record is deleted, and we also deleted one child record, but the second child record is in the database and the table has a foreign key which references to the parent table:
SELECT * FROM P1;
SELECT * FROM C1;

9 komentarjev:

  1. Neviđeno

    Be careful, Anonymous visited your blog.

    OdgovoriIzbriši
  2. Hehe, I like that "Anonymous" reads my blog ;)

    OdgovoriIzbriši
  3. I also tested this on "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production" and the problem is still there.

    OdgovoriIzbriši
  4. Submitted a bug to Oracle "SR 3-5376417071: Problem when using deferred constraints"...

    OdgovoriIzbriši
  5. Got a response from Oracle:
    Bug 13798806 has been created for this issue.

    OdgovoriIzbriši
  6. Got a solution from Oracle: alter session set "_fix_control"='9547706:OFF';

    OdgovoriIzbriši