Prevent Validation of Check Constraints in 11.2.0.2

11.2.0.2 has a great new “feature”: you can disable the validation of a check contraint during an update !

how does it work ?
it is a bit like magic: when an update violates a check constraint, just use a merge-statement to update the table.

See:

sokrates@11.2.0.2 > create table oops ( i int primary key, n number constraint ch_n_sm_5 check( n < 5 ));

Table created.

sokrates@11.2.0.2 > insert into oops values(1, 2);

1 row created.

sokrates@11.2.0.2 > insert into oops values(2, 4.5);

1 row created.

sokrates@11.2.0.2 > commit;

Commit complete.

sokrates@11.2.0.2 > update oops set n = n * 7 where i = 1;

update oops set n = n * 7 where i = 1
*
ERROR at line 1:
ORA-02290: check constraint (SOKRATES.CH_N_SM_5) violated

so far, so good. now the trick:

sokrates@11.2.0.2 > merge into oops using (select 1 d from dual) d on (d.d = oops.i) when matched then update set n = n * 7;

1 row merged.

sokrates@11.2.0.2 > REM oops !!!

sokrates@11.2.0.2 > select i, n from oops;

I        N
———- ———-
1       14
2      4.5

sokrates@11.2.0.2 > commit;

Commit complete.

Now, isn’t that a great new feature ?

What does Oracle say ?

Bug 9285259: MERGE IGNORES CHECK CONSTRAINT

says:
“
…
Fixed in Product Version 12.1
…

WORKAROUND:
-----------
none

“

So, in my opinion, if you use “merge” to update rows, you have a real problem and probably your best decision could be “don’t use 11.2 at all, wait until they have fixed that behaviour in 12.1″

Remarks.

I can reproduce the Bug on 11.2.0.2 on Linux x86-64

According to the metalink note mentioned above, it affects also 11.2.0.1, which I haven’t checked.

Furthermore, I don’t know if it’s only specific to that platform.

About these ads